CODEWITHSUNDEEP
sqlsql-server-2016
Learner1111
Learner1111

Reputation: 39

How to generate custom sales analysis report in sq

Suppose my table is

CREATE TABLE SALESS (id int, product int, salesdate DATETIME, amount float)
INSERT INTO SALESS VALUES 
(1, 1, '2020-10-10', 500),
(1, 1, '2020-11-10', 300),
(1, 1, '2020-12-10', 200),
(1, 2, '2020-10-10', 300),
(1, 2, '2020-11-10', 1000),
(1, 2, '2020-12-10', 200)

and my query is

select product, year(salesdate) 'year', month(salesdate) 'month', sum(amount)'total maount' from SALESS
group by product, year(salesdate), month(salesdate)
order by product, year(salesdate), month(salesdate)

Giving me the result

current result

What I need as result is like below, how can modify my query to give me this result?

the needed result

Upvotes: 0

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You can use conditional aggregation:

select year(salesdate) as year, month(salesdate) as month, 
       sum(case when product = 1 then amount else 0 end) as amount_1,
       sum(case when product = 2 then amount else 0 end) as amount_2
from SALESS
group by year(salesdate), month(salesdate)
order by year(salesdate), month(salesdate);

If you really want to add three empty rows for 2021, you can add this before the order by:

union all
select v.year, null, null
from (values (2021), (2021), (2021)) v(year)

Upvotes: 2

Related Questions