Sample Header Ad - 728x90

Query To Show YTD Total Summing Each Month Prior To Current

1 vote
1 answer
187 views
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
Asked by jamesMandatory (69 rep)
Jul 19, 2020, 07:12 PM
Last activity: Jun 28, 2025, 12:09 PM