Group by multiple fields after SQL join-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

Probably something like this:

SELECT users.name, tasks.completed_by month, sum(COALESCE(tasks.cost, 0)) cost_sum
FROM users
JOIN tasks
ON tasks.assignee = users.id
WHERE completed IS NOT NULL AND assignee IS NOT NULL
group by users.name, tasks.completed_by