MySql Group and sum sales by by month for each departments across the year joining multiple tables

Question

I've been trying to achieve the following to no avail. Any help will be appreciated.

Departments - Departments within an organization

id	department_name
1	Process Consulting
2	Technical Consulting
3	Administrative Unit

Services - Each service rendered is mapped to a department

id	service_name	department_id
1	Project management	1
2	Video production	2
3	Training	2
4	Facility Rental	3

Transactions - Each transaction is mapped to a service

id	transaction_amount	service_id	transaction_date
1	2000	1	2021-02-04
2	3000	4	2021-01-01
3	1500	2	2021-03-14
4	3500	2	2021-03-20

The result I would like to achieve

department_id	department_name	year_summary (Jan - Dec separated by comma)
1	Process Consulting	0,2000,0,0,0,0,0,0,0,0,0,0
2	Technical Consulting	0,0,5000,0,0,0,0,0,0,0,0,0
3	Administrative Unit	3000,0,0,0,0,0,0,0,0,0,0,0

Answer 1

You seem to want conditional aggregation at two levels. The first is a summary by department and month:

select d.id, d.department_name, month(transaction_date) as mon,
       sum(t.transaction_amount) as amount
from departments d left join
     services s
     on s.department_id = d.id left join
     transactions t
     on t.service_id = s.id  
where t.transaction_date >= '2021-01-01' and
      t.transaction_date < '2022-01-01'   
group by d.id, d.department_name;

The second then aggregates that in the format you want:

select id, department_name,
       concat_ws(',',
                 max(case when mon = 1 then amount else 0 end),
                 max(case when mon = 2 then amount else 0 end),
                 . . .   -- fill in the rest of the months
                 max(case when mon = 12 then amount else 0 end)
                ) as year_summary
from (select d.id, d.department_name, month(transaction_date) as mon,
             sum(t.transaction_amount) as amount
      from departments d left join
           services s
           on s.department_id = d.id left join
           transactions t
           on t.service_id = s.id  
      where t.transaction_date >= '2021-01-01' and
            t.transaction_date < '2022-01-01'   
      group by d.id, d.department_name
     ) dm
group by id, department_name;

Answer 2

Though you have already got your answer I am sharing here another one since it's already prepared:

Schema and insert statements:

 create table Departments(id int, department_name varchar(100));
 insert into Departments values(1,  'Process Consulting');
 insert into Departments values(2,  'Technical Consulting');
 insert into Departments values(3,  'Administrative Unit');  
 
 create table Services (id int, service_name varchar(100), department_id int);
 insert into Services values(1, 'Project management',   1);
 insert into Services values(2, 'Video production'  ,   2);
 insert into Services values(3, 'Training'      ,   2);
 insert into Services values(4, 'Facility Rental'   ,   3);
 
 create table Transactions(id int, transaction_amount int, service_id int, transaction_date date);
 insert into Transactions values(1, 2000,   1,  '2021-02-04');
 insert into Transactions values(2, 3000,   4,  '2021-01-01');
 insert into Transactions values(3, 1500,   2,  '2021-03-14');
 insert into Transactions values(4, 3500,   2,  '2021-03-20');

Query (with common table expression and group_concat() ):

 with Calendar as
   (
   SELECT 1 mon UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
   SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
   SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
   )
   , d as (select * from Calendar cross join Departments )
   , t as (select d.id,d.department_name ,month(transaction_date)mon, sum(transaction_amount)amountsum  from Departments d inner join Services s on d.id=s.department_id
 inner join Transactions t on s.id=t.service_id
 group by  d.id,d.department_name 
              )
              
 select d.id,d.department_name,group_concat(coalesce(amountsum,0) order by d.mon)  year_summary
 from d left join t on d.id=t.id and t.mon=d.mon
 group by d.id
              
              
              
   

Output:

id	department_name	year_summary
1	Process Consulting	0,2000,0,0,0,0,0,0,0,0,0,0
2	Technical Consulting	0,0,5000,0,0,0,0,0,0,0,0,0
3	Administrative Unit	3000,0,0,0,0,0,0,0,0,0,0,0

db<fiddle here