By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE promotion (
promotion Varchar(4)
UNIQUE
);
INSERT INTO promotion VALUES (null),('10%'),('20%'),('30%');
CREATE TABLE sale (
sale_id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
promotion Varchar(4),
date_ Date NOT NULL,
amount int
);
insert into sale (promotion, date_, amount) values
(null,'2022-1-1',100),
('10%','2022-1-1',100),
('20%','2022-1-1',100),
('30%','2022-1-1',100),
(null,'2022-1-2',100),
('10%','2022-1-2',110),
('20%','2022-1-2',120),
('30%','2022-1-2',130),
(null,'2022-2-1',75),
('10%','2022-2-1',75),
('30%','2022-2-1',75),
(null,'2022-2-2',75),
('10%','2022-2-2',75),
('30%','2022-2-2',75);
select
Month(date_) month_,
ifnull(p.promotion,'No Promo') Promotion,
sum(amount) Total
from sale
left join promotion p on p.promotion = sale.promotion
group by Month(date_), ifnull(p.promotion,'No Promo');
| month_ | Promotion | Total |
|---|---|---|
| 1 | No Promo | 200 |
| 1 | 10% | 210 |
| 1 | 20% | 220 |
| 1 | 30% | 230 |
| 2 | No Promo | 150 |
| 2 | 10% | 150 |
| 2 | 30% | 150 |
select
m,
ifnull(p.promotion,'No Promo') Promotion,
sum(amount) Total
from (select distinct month(date_) m from sale) m
cross join
promotion p
left join sale s on m.m = month(date_) and p.promotion = s.promotion
group by m, ifnull(p.promotion,'No Promo')
order by m, ifnull(p.promotion,'No Promo');;
| m | Promotion | Total |
|---|---|---|
| 1 | 10% | 210 |
| 1 | 20% | 220 |
| 1 | 30% | 230 |
| 1 | No Promo | null |
| 2 | 10% | 150 |
| 2 | 20% | null |
| 2 | 30% | 150 |
| 2 | No Promo | null |