I need help writing a query that shows the amount of orders for each month of the year and also the cumulative amount each year

I am so close to answering this question, but can’t figure out the final bits. I am using the AdventureWorks2019 database.

My code is:

with cte as
( 
   Select 
   year(orderdate) as "Years",
   month(orderdate) as "Months",
   sum(SubTotal) as "MonthlySales", 
   datepart(m, orderdate) as "MonthNumber" 
   from sales.SalesOrderHeader 
   group by year(orderdate), month(orderdate)
) 
select 
Years, Months, MonthlySales as "MonthlySales", sum(MonthlySales) over (partition by months order by MonthlySales
                                                                         rows between unbounded preceding and current row) as "Cumulative"
from cte  
group by Years, Months, MonthlySales
ORDER by Years, Months

I cant figure out how to order the ‘over’ part of the query. I know it needs to be “rows between unbounded preceding and current row”, but because MonthlySales is sum(totaldue), adding it to another SUM() probably messes it up.

Honestly this question is pretty tough, and I gave it my best. Thank you for your help!

Answer

Please check and it’ll return desired output.

-- SQL SERVER
SELECT t.OrderYear
     , CASE WHEN t.OrderMonth IS NULL THEN 'Grand Total' ELSE CAST(t.OrderMonth AS VARCHAR(20)) END b
     , t.MonthlySales
     , MAX(t.cum_total) cum_total
FROM (SELECT
    YEAR(OrderDate) AS OrderYear,
    MONTH(OrderDate) AS OrderMonth,
    SUM(SubTotal) AS MonthlySales,
    SUM(SUM(SubTotal)) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate) ROWS UNBOUNDED PRECEDING) cum_total
    
FROM Sales.SalesOrderHeader
GROUP BY GROUPING SETS ((YEAR(OrderDate), MONTH(OrderDate)))) t
GROUP BY GROUPING SETS ((t.OrderYear
     , t.OrderMonth
     , t.MonthlySales), t.OrderYear);   

Also Check data from https://dbfiddle.uk/?rdbms=sqlserver_2019&sample=adventureworks&fiddle=e6cd2ba8114bd1d86b8c61b1453cafcf