SQL query construction - ORDER BY SUM () fields in rows that satisfy a specific condition

Question

OK, so I have two tables that I work with - projectand servicesimplified in this way:

project
-------  
id PK  
name str

service  
------- 
project_id FK for project  
time_start int (timestamp)  
time_stop int (timestamp)  

One-to-many relationship.

Now I want to return (preferably with a single request) a list of an arbitrary number of projects, sorted by the total amount of time spent on them, which is SUM(time_stop) - SUM(time_start) WHERE project_id =something.

I still have

SELECT project.name  
FROM service  
LEFT JOIN project ON project.id = service.project_id  
LIMIT 100

but I can’t figure out how to do this ORDER BY.

Answer 1

You need a group:

SELECT project.name
FROM service
LEFT JOIN project ON project.id = service.project_id
GROUP BY project_id
ORDER BY SUM(time_stop - time_start)
LIMIT 100