Query design in SQL - ORDER BY SUM() of field in rows which meet a certain condition

Question

OK, so I have two tables I'm working with - project and service, simplified thus:
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 one query) a list of an arbitrary number of projects, sorted by the total amount of time spent at them, which is found by SUM(time_stop) - SUM(time_start) WHERE project_id = something.

So far, I have
SELECT project.name
FROM service
LEFT JOIN project ON project.id = service.project_id
LIMIT 100

but I cannot figure out how what to ORDER BY.

Answer 1

You need a GROUP BY:

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