CODEWITHSUNDEEP
sqlsql-servert-sql
David
David

Reputation: 279

T-SQL query to summarize sales for ALL YEARS: with total per month per year, and cumulative monthly amounts

I need to create a Sales Report that shows all years sales per month, and cumulative sales. The database table is simple: 

Transactions 
(
    ID INT, 
    TransactionDate DATETIME, 
    SalesAmount MONEY
)

I want the results to look similar to ExcelSheet below (I am showing only 2017/2018 amounts, but actual query needs to return results for all available years according to TransactionDate)

enter image description here

Upvotes: 0

Views: 480

Answers (3)

Foad Alavi
Foad Alavi

Reputation: 62

Try it:

With Q 
as
(
   Select DatePart(yyyy,TransactionDate) 'Year',DatePart(m,TransactionDate) 'Month',  sum(SalesAmount) 'Sales'
   From Transactions 
   Group by DatePart(yyyy,TransactionDate),DatePart(m,TransactionDate)
) 
Select q.Year,q.Month,q.sales,( Select sum(q1.Sales)
        From Q q1
        Where q1.Year=q.Year
         And  q1.Month <= q.Month
        ) 'Cumulative Sale'
From Q q
Order by q.Year,q.Month

Upvotes: 1

zip
zip

Reputation: 4061

Try this:

with cte as
(
select year(TransactionDate) as Year, month(TransactionDate) as Month, SalesAmount 

)

 select a.Year, a.Month, a.SalesAmount, sum(b.SalesAmount) as cumulativeSalesAmount 
 from Transactions  a inner join Transactions  b on a.STORE_ID = b.STORE_ID and a.Year = b.Year and a.Month >= b.Month
 group by a.Year, a.Month
 order by 1, 2

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269823

This is aggregation and a cumulative sum:

select year(TransactionDate), month(TransactionDate),
       sum(SalesAmount),
       sum(sum(SalesAmount)) over (partition by year(TransactionDate) order by min(TransactionDate))
from Transactions
group by year(TransactionDate), month(TransactionDate)
order by year(TransactionDate), month(TransactionDate);

Upvotes: 3

Related Questions