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:

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

I now want to go further and find the total cost grouped by name and month completed. However, I can't figure out the syntax for the GROUP BY after the current select and WHERE clauses. Ultimately, I want the query to return something like this:

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

I've tried various combinations and nested GROUP BY clauses but can't seem to get the results I want. Any pointers would be greatly appreciated.

Answer 1

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

Answer 2

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