Mysql – Query To Show YTD Total Summing Each Month Prior To Current

cteMySQLmysql-5.6

I need a query to give a YTD total that should sum each month before it. For example, January YTD would equal January Total, February YTD would equal January Total + February Total, March YTD would equal January Total + February Total + March Total. My issue is that my query is showing the SUM() of the Total for all months.

This is DDL that illustrates my issue

Create Table empSales
(
    spendType varchar(100)
    ,spendAmt decimal(10,2)
    ,spendMonth varchar(100)
);

Insert Into `empSales` (`spendType`, `spendAmt`, `spendMonth` ) VALUES
('James', '1.00', 'January'),
('Richard', '3.28', 'January'),
('Barb', '4.13', 'January'),
('James', '3.00', 'February'),
('Richard', '3.28', 'February'),
('Barb', '4.13', 'February'),
('James', '2.00', 'March'),
('Richard', '5.28', 'March'),
('Barb', '7.13', 'March');

And this is the query I tried that only gives the overall total instead of just the YTD total I'm after

with MonthAgg as (
select IFNULL(spendType,'Total') spendType 
, Sum(Case when spendMonth='January' then spendAmt else null end ) January
, Sum(Case when spendMonth='February' then spendAmt else null end ) February 
, Sum(Case when spendMonth='March' then spendAmt else null end ) March
, Sum(Case when spendMonth='April' then spendAmt else null end ) April 
, Sum(Case when spendMonth='May' then spendAmt else null end ) May
, Sum(Case when spendMonth='June' then spendAmt else null end ) June
, Sum(Case when spendMonth='July' then spendAmt else null end ) July
, Sum(Case when spendMonth='August' then spendAmt else null end ) August 
, Sum(Case when spendMonth='September' then spendAmt else null end ) September
, Sum(Case when spendMonth='October' then spendAmt else null end ) October
, Sum(Case when spendMonth='November' then spendAmt else null end ) November 
, Sum(Case when spendMonth='December' then spendAmt else null end ) December
FROM empSales
GROUP BY spendType WITH ROLLUP)
, mycteSum as (
select spendMonth , Sum(spendAmt) Amt 
FROM empSales
GROUP BY spendMonth
)
,mycteYTD as (
select spendMonth , sum(Amt) Over(order by Cast(spendMonth+ ' 01, 1900' as date) ) YTD
from mycteSum)
Select * from MonthAgg
UNION ALL
Select 'YTD' as summarySpend,
max(Case when spendMonth='January' then YTD else null end ) JanuaryYTD 
, max(Case when spendMonth='February' then YTD else null end ) FebruaryYTD 
, max(Case when spendMonth='March' then YTD else null end ) MarchYTD 
, max(Case when spendMonth='April' then YTD else null end ) AprilYTD 
, max(Case when spendMonth='May' then YTD else null end ) MayYTD 
, max(Case when spendMonth='June' then YTD else null end ) JuneYTD 
, max(Case when spendMonth='July' then YTD else null end ) JulyYTD 
, max(Case when spendMonth='August' then YTD else null end ) AugustYTD 
, max(Case when spendMonth='September' then YTD else null end ) SeptemberYTD 
, max(Case when spendMonth='October' then YTD else null end ) OctoberYTD 
, max(Case when spendMonth='November' then YTD else null end ) NovemberYTD 
, max(Case when spendMonth='December' then YTD else null end ) DecemberYTD 
from mycteYTD

Best Answer

It is always a bad idea to save Dates Month as rtext mysql really has issues with that

Your approach what very good, but i had a problem to get the right order

with MonthAgg as (
select IFNULL(spendType,'Total') spendType 
, Sum(Case when spendMonth='January' then spendAmt else null end ) January
, Sum(Case when spendMonth='February' then spendAmt else null end ) February 
, Sum(Case when spendMonth='March' then spendAmt else null end ) March
, Sum(Case when spendMonth='April' then spendAmt else null end ) April 
, Sum(Case when spendMonth='May' then spendAmt else null end ) May
, Sum(Case when spendMonth='June' then spendAmt else null end ) June
, Sum(Case when spendMonth='July' then spendAmt else null end ) July
, Sum(Case when spendMonth='August' then spendAmt else null end ) August 
, Sum(Case when spendMonth='September' then spendAmt else null end ) September
, Sum(Case when spendMonth='October' then spendAmt else null end ) October
, Sum(Case when spendMonth='November' then spendAmt else null end ) November 
, Sum(Case when spendMonth='December' then spendAmt else null end ) December
FROM empSales
GROUP BY spendType WITH ROLLUP)
, mycteSum as (
select DISTINCT spendMonth , Sum(spendAmt) Over(PARTITION by spendMonth  order by MONTH(STR_TO_DATE(CONCAT("01.",spendMonth,".1990"),"%d.%M.%Y"))) Amt
FROM empSales
)
,mycteYTD as (
select spendMonth , sum(Amt) Over( ORDER BY MONTH(STR_TO_DATE(CONCAT("01.",spendMonth,".1990"),"%d.%M.%Y")) 
ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) YTD
from mycteSum)
Select * from MonthAgg
UNION ALL
Select 'ytd' as summarySpend,
max(Case when spendMonth='January' then YTD else null end ) JanuaryYTD 
, max(Case when spendMonth='February' then YTD else null end ) FebruaryYTD 
, max(Case when spendMonth='March' then YTD else null end ) MarchYTD 
, max(Case when spendMonth='April' then YTD else null end ) AprilYTD 
, max(Case when spendMonth='May' then YTD else null end ) MayYTD 
, max(Case when spendMonth='June' then YTD else null end ) JuneYTD 
, max(Case when spendMonth='July' then YTD else null end ) JulyYTD 
, max(Case when spendMonth='August' then YTD else null end ) AugustYTD 
, max(Case when spendMonth='September' then YTD else null end ) SeptemberYTD 
, max(Case when spendMonth='October' then YTD else null end ) OctoberYTD 
, max(Case when spendMonth='November' then YTD else null end ) NovemberYTD 
, max(Case when spendMonth='December' then YTD else null end ) DecemberYTD 
from mycteYTD

spendType | January | February | March | April |  May | June | July | August | September | October | November | December
:-------- | ------: | -------: | ----: | ----: | ---: | ---: | ---: | -----: | --------: | ------: | -------: | -------:
Barb      |    4.13 |     4.13 |  7.13 |  null | null | null | null |   null |      null |    null |     null |     null
James     |    1.00 |     3.00 |  2.00 |  null | null | null | null |   null |      null |    null |     null |     null
Richard   |    3.28 |     3.28 |  5.28 |  null | null | null | null |   null |      null |    null |     null |     null
Total     |    8.41 |    10.41 | 14.41 |  null | null | null | null |   null |      null |    null |     null |     null
ytd       |    8.41 |    18.82 | 24.82 |  null | null | null | null |   null |      null |    null |     null |     null

db<>fiddle here

Related Question