mysql - sums per months/years for every row per year

Question

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.

Seriously consider handling issues of data display in application code — Strawberry, Apr 24 '20 at 12:15
Arguably, every part of the problem not addressed by the following query is 'an issue of data display'... `SELECT DATE_FORMAT(app_date,'%Y-%m') yearmonth, work_id, SUM(app_price) app_price_total, SUM(app_price_paid) app_price_paid_total FROM tblappointment GROUP BY yearmonth,work_id;` — Strawberry, Apr 24 '20 at 12:27
Also, note that 'price' is rarely DOUBLE; it's why DECIMAL was invented — Strawberry, Apr 24 '20 at 12:35

score 1 · Accepted Answer · answered Apr 24 '20 at 12:25

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

mysql - sums per months/years for every row per year

1 Answers1