Search code examples
mysql

MySQL SUM and sort by a date range

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.

enter image description here

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?

Solution

  • You want the hours of DATE '2024-05-06'. You can get them with conditional aggregation

    SELECT
  jobno,
  COALESCE(SUM(CASE WHEN gusTime.date = DATE '2024-05-06' THEN hours END), 0) AS weekHours
FROM gusTime
WHERE gusTime.date >= DATE '2024-04-29'
GROUP by jobno
ORDER BY weekHours DESC;