I have a table with a bunch of dates and hours per date (kind of like a timesheet).
I can sort by project name, but I want to be able to sort by a specific week.
For example, if you click on the May 6th header, you’d get Project 5, then Project 3, and then the other projects without time.

I can do this:

SELECT DISTINCT jobno, COALESCE(SUM(hours), 0) AS weekHours
FROM gusTime
WHERE gusTime.date>='2024-04-29'
GROUP by jobno
ORDER BY weekHours DESC;

But then weekHours is the total for all the weeks.

So, I tried this:

SELECT DISTINCT jobno, COALESCE(SUM(hours), 0) AS weekHours
FROM gusTime
WHERE gusTime.date>='2024-04-29'
AND (gusTime.date BETWEEN '2024-05-06' AND '2024-05-10')
GROUP by jobno
ORDER BY weekHours DESC;

But that only returns Projects 3 & 5, and the projects with no time don’t show up anymore.
How can I do the weekHours SUM for a specific week & sort by it, and keep the projects with no time?