0

this query is functional. I wanted to add a count of packages that has status = 100.

I'm running this query at the homepage to get all the routes and the number of packages a driver ID 1 has. On the same page, I also wanted to get the number of completed packages

Is it possible to do it in one query?

SELECT r.ID, r.Name, r.Date, count(p.Route_ID) as Num_Package
FROM drvapp_routes as r 
INNER join drvapp_packages as p
ON p.Route_ID = r.ID 
WHERE r.Driver_ID = 1 
GROUP BY r.ID
ORDER by r.Name ASC 

Route Table

+---------+-----------------
| ID | Driver_ID | Name
+---------+-----------------
| 1  |      1001 | Route 1    
| 2  |      1001 | Route 2   
| 3  |      1001 | Route 3  
+---------+-----------------

Package Table

+---------+---------------
| ID | Route ID  | Status
+---------+---------------
| 1  |      1    |   100
| 2  |      2    |   70  
| 3  |      3    |   50
| 4  |      3    |   100
| 5  |      1    |   100
+---------+---------------


Result should be 

+---------+-----------------------------------------
| ID | Route Name | Num_Package | Completed_Route
+---------+-----------------------------------------
| 1  |    Route 1 |       2     |               2
| 2  |    Route 2 |       1     |               0
| 3  |    Route 3 |       2     |               1
+---------+-----------------------------------------
Adrian Roy Baguio
  • 104
  • 10
  • 4
    Help us help you - share the tables' structures, some sample data, and the result you want to get for it – Mureinik Dec 15 '20 at 21:12
  • Please see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query for SQL related questions. – Progman Dec 15 '20 at 21:23
  • 1
    Add `SUM(status = 100) as num_status100` to the `SELECT` list. – Barmar Dec 15 '20 at 21:27

0 Answers0