1

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
  • You've tagged your question with MySQL 5.6 but you use CTEs but they only appeared in version 8.01? Which version are you using? – Vérace Jul 20 at 1:06
0

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

| improve this answer | |
  • i can change DDL of the table. What datatype would you recommend? – jamesMandatory Jul 19 at 21:00
  • as you see it runs now with ORDER BY MONTH(STR_TO_DATE(CONCAT("01.",spendMonth,".1990"),"%d.%M.%Y")) and that will be the greqatest problem for you, forme it is only code that has to be converted. A date datatype could make the code a bit less big, but you must think in the following years if you wnat to displays last years data, but as far as i can see it is solved your problem – nbk Jul 19 at 21:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.