Group by multiple fields after SQL join

Question

I wrote the following query that correctly joins two tables that show the number of tasks completed by individuals on the team and the associated costs of those tasks: SELECTusers.idASuser_id,users.name,COALESCE(tasks.cost,0)AScost, tasks.assignee,tasks.completed,tasks.completed_byFROMusersJOINtasksONtasks.assignee=use

P粉883278265 · Answer

It looks 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

P粉674999420 · Answer

Join users to a query that aggregates in tasks and returns the total monthly cost 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;

There is no need to check whether completed is null or assignee is null, because nulls are used here Filtered out:

WHERE YEAR(completed) = 2022

here:

ON t.assignee = u.id

User ID	Name	Assignee	cost	completed	Complete time
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	oli	9	0.25	2022-03-03 02:38:41	9

Name	cost_sum	moon
Mike	62	January
Katie	20	January
Jim	15	January
oli	45	January
Mike	17	February