mysql - sums per month and year

Question

i have an mysql table with the following structure:

tblapp
app_id  app_date  app_price  app_price_paid  payment_id      receipt_id  expenses_enabled
  1    01/01/2000     100         50              1                1               1
  1    11/01/2000      10         20              3                2               0
  1    21/01/2000      40         40              1                4               0
  1    30/01/2000      30         30              2                1               1

I would like to have as columns

YEAR  DETAILS  Jan  Feb ... Dec TOTALS

and rows

1. Price     = sum app_price where expenses_enabled=1
2. PricePaid = sum app_price_paid where expenses_enabled=1
3. No        = sum app_price where expenses_enabled=1 and receipt_id=1
3. Yes       = sum app_price where expenses_enabled=1 and receipt_id=0

Desire output:

Year Details     Jan Feb ... Dec Totals
2020  Price      130 0  ...  0    130
2020  Price_Paid  80 0  ...  0     80
2020  No          80 0  ...  0     80
2020  Yes          0 0  ...  0      0

Is it possible.

user_13364261 · Answer

Try this one:

select 
    y.year,
    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) = 12 then val else 0 end) month_12,
    sum(case when month(app_date) > 0  then val else 0 end) total
FROM (
    SELECT 'Price' details UNION ALL 
    SELECT 'PricePaid' details UNION ALL
    SELECT 'No' details UNION ALL
    SELECT 'Yes' details  
) d cross join (
    SELECT distinct year(app_date) yr
    FROM tblapp 
) y    
left join (  
        select app_date, COALESCE(app_price, 0) val, 'Price' details from tblapp 
        WHERE expenses_enabled = 1
    union all
        select app_date, COALESCE(app_price_paid, 0) val, 'PricePaid' details from tblapp 
        WHERE expenses_enabled = 1
    union all
        select app_date, COALESCE(app_price, 0) val, 'No' details from tblapp 
        WHERE expenses_enabled = 1 and receipt_id = 1
    union all
        select app_date, COALESCE(app_price, 0) val, 'Yes' details from tblapp 
        WHERE expenses_enabled = 1 and receipt_id = 0
) t on year(t.app_date) = y.year and t.details = d.details
group by y.year, d.details
order by y.year desc;

mysql - sums per month and year

1 Answers