I have written the following query which correctly joins two tables which shows the number of completed tasks by individuals in a team and the associated cost of those tasks:
SELECT users.id AS user_id,
users.name,
COALESCE(tasks.cost, 0) AS cost,
tasks.assignee,
tasks.completed,
tasks.completed_by
FROM users
JOIN tasks
ON tasks.assignee = users.id
WHERE completed IS NOT NULL AND assignee IS NOT NULL
This provides the following table:
| user id | name | asignee | cost | completed | completed_by |
|---|---|---|---|---|---|
| 18 | mike | 8 | 0.25 | 2022-01-24 19:54:48 | 8 |
| 13 | katie | 13 | 0 | 2022-01-24 19:55:18 | 8 |
| 13 | katie | 13 | 0 | 2022-01-25 11:49:53 | 8 |
| 12 | jim | 12 | 0.5 | 2022-01-25 11:50:02 | 12 |
| 9 | ollie | 9 | 0.25 | 2022-03-03 02:38:41 | 9 |
I would now like to further find the SUM of cost, grouped by name and the month completed. However, I can't work out the syntax for the GROUP BY after my current select and WHERE clause. Ultimately, I would like the query to return something like this:
| name | cost_sum | month |
|---|---|---|
| mike | 62 | January |
| katie | 20 | January |
| jim | 15 | January |
| ollie | 45 | January |
| mike | 17 | February |
I have tried various combinations and nesting GROUP BY clauses but I can't seem to get the desired result. Any pointers would be greatly appreciated.
Join users to a query that aggregates in tasks and returns the total cost per month for a specific year:
SELECT u.name,
COALESCE(t.cost, 0) AS cost,
DATE_FORMAT(t.last_day, '%M')
FROM users u
INNER JOIN (
SELECT assignee, LAST_DAY(completed) last_day, SUM(cost) AS cost
FROM tasks
WHERE YEAR(completed) = 2022
GROUP BY assignee, last_day
) t ON t.assignee = u.id
ORDER BY t.last_day;
No need to check if completed is null or assignee is null, because nulls are filtered out here:
WHERE YEAR(completed) = 2022
and here:
ON t.assignee = u.id