I have the following demo
CREATE TABLE `tblappointment` (
`app_id` mediumint(8) UNSIGNED NOT NULL,
`app_date` date NOT NULL,
`work_id` smallint(5) UNSIGNED NOT NULL,
`app_price` double DEFAULT NULL,
`app_price_paid` double DEFAULT NULL,
`receipt_id` tinyint(3) UNSIGNED DEFAULT NULL
);
INSERT INTO `tblappointment` (`app_id`, `app_date`, `work_id`, `app_price`, `app_price_paid`, `receipt_id`) VALUES
("1", "2020-03-01", "21", "100", "50", "1"),
("2", "2020-04-01", "21", "200", "40", "3"),
("4", "2020-06-01", "2", "500", "70", "1"),
("5", "2020-07-01", "21", "300", "30", "1"),
("6", "2020-08-01", "21", "200", "20", "2"),
("7", "2020-09-01", "5", "100", "50", "1"),
("8", "2020-10-01", "6", "200", "30", "2"),
("9", "2020-11-01", "21", "300", "30", "1"),
("10", "2020-12-01", "21", "400", "20", "3"),
("11", "2020-01-01", "8", "500", "90", "1"),
("12", "2020-02-01", "21", "600", "80", "5"),
("13", "2021-03-01", "3", "700", "70", "1");
sql:
select
year(app_date) yr,
details,
sum(case when month(app_date) = 1 then val else 0 end) month_01,
sum(case when month(app_date) = 2 then val else 0 end) month_02,
sum(case when month(app_date) = 3 then val else 0 end) month_03,
sum(case when month(app_date) = 4 then val else 0 end) month_04,
sum(case when month(app_date) = 5 then val else 0 end) month_05,
sum(case when month(app_date) = 6 then val else 0 end) month_06,
sum(case when month(app_date) = 7 then val else 0 end) month_07,
sum(case when month(app_date) = 8 then val else 0 end) month_08,
sum(case when month(app_date) = 9 then val else 0 end) month_09,
sum(case when month(app_date) = 10 then val else 0 end) month_10,
sum(case when month(app_date) = 11 then val else 0 end) month_11,
sum(case when month(app_date) = 22 then val else 0 end) month_12
from (
select app_date, app_price val, 'work' details from tblappointment where work_id = 21
union all
select app_date, app_price_paid val, 'paid' details from tblappointment where work_id = 21
union all
select app_date, app_price - app_price_paid val, 'debt' details from tblappointment where work_id = 21
union all
select app_date, app_price val, 'test' details from tblappointment where work_id = 3
) t
group by yr, details
Output Now:
YEAR DETAILS 1 2 3 4 5 6 7 8 9 10 11 12
2020 Work numbers here....
2020 Paid numbers here....
2020 Debt numbers here....
2021 Test numbers here....
I would like to have this output:
YEAR DETAILS 1 2 3 4 5 6 7 8 9 10 11 12
2020 Work numbers here....
2020 Paid numbers here....
2020 Debt numbers here....
2020 Test NULL or O or Empty
2020 Work NULL or O or Empty
2020 Paid NULL or O or Empty
2020 Debt NULL or O or Empty
2021 Test numbers here....
As you can see, 'Test' has no values on year 2020 and only in 2021. And the rest, have values on 2020 but not data on 2021. Need to have all 'details' display every year, either with NULL or 0 or Empty.
Thank you.
You can join the distinct years and all the details and then left join to your query:
select
y.yr,
d.details,
sum(case when month(app_date) = 1 then val else 0 end) month_01,
sum(case when month(app_date) = 2 then val else 0 end) month_02,
sum(case when month(app_date) = 3 then val else 0 end) month_03,
sum(case when month(app_date) = 4 then val else 0 end) month_04,
sum(case when month(app_date) = 5 then val else 0 end) month_05,
sum(case when month(app_date) = 6 then val else 0 end) month_06,
sum(case when month(app_date) = 7 then val else 0 end) month_07,
sum(case when month(app_date) = 8 then val else 0 end) month_08,
sum(case when month(app_date) = 9 then val else 0 end) month_09,
sum(case when month(app_date) = 10 then val else 0 end) month_10,
sum(case when month(app_date) = 11 then val else 0 end) month_11,
sum(case when month(app_date) = 22 then val else 0 end) month_12
from (
select 'work' details union all select 'paid' union all
select 'debt' details union all select 'test' details
) d cross join (
select distinct year(app_date) yr
from tblappointment
where work_id in (3, 21)
) y
left join (
select app_date, app_price val, 'work' details from tblappointment where work_id = 21
union all
select app_date, app_price_paid val, 'paid' details from tblappointment where work_id = 21
union all
select app_date, app_price - app_price_paid val, 'debt' details from tblappointment where work_id = 21
union all
select app_date, app_price val, 'test' details from tblappointment where work_id = 3
) t on year(t.app_date) = y.yr and t.details = d.details
group by y.yr, d.details
See the demo.
Results:
> yr | details | month_01 | month_02 | month_03 | month_04 | month_05 | month_06 | month_07 | month_08 | month_09 | month_10 | month_11 | month_12
> ---: | :------ | -------: | -------: | -------: | -------: | -------: | -------: | -------: | -------: | -------: | -------: | -------: | -------:
> 2020 | debt | 0 | 520 | 50 | 160 | 0 | 0 | 270 | 180 | 0 | 0 | 270 | 0
> 2020 | paid | 0 | 80 | 50 | 40 | 0 | 0 | 30 | 20 | 0 | 0 | 30 | 0
> 2020 | test | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
> 2020 | work | 0 | 600 | 100 | 200 | 0 | 0 | 300 | 200 | 0 | 0 | 300 | 0
> 2021 | debt | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
> 2021 | paid | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
> 2021 | test | 0 | 0 | 700 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
> 2021 | work | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0