Grouping sets by year, month, and date

Question

Is it possible to rollup the date, month, and year using GROUPING SETS, where the dates are shownn and grouped into month, and year, and the value for month and year are displayed instead of being null?

Here is the sample data, and my attempt at using grouping sets

create table #tbl_data
(
employeeId int
,productLine int
,salesDate datetime
,salesTotal money
)

insert into #tbl_data
values
(1,1,'02/09/2017',199)
,(1,1,'04/10/2017',379)
,(2,1,'04/21/2017',323)
,(2,1,'04/10/2017',461)
,(2,1,'06/11/2017',304)
,(3,1,'01/28/2017',147)
,(3,1,'02/09/2017',320)
,(4,1,'03/07/2017',344)
,(4,1,'03/13/2017',176)

select
    productLine, 
    employeeId, 
    salesDate, 
    mon=month(salesdate),
    yr=year(salesdate), 
    sum(salesTotal) as salesTotal
from 
    #tbl_data

group by grouping sets
(
    (productLine, employeeId, salesDate)
    ,(productLine, employeeId)
    ,(productLine)
)

Here is what returned by the query (left) and what i wanted to accomplish (right)

@RenatoAfonso, can you suggest something more specific? I tried cube and rollup - without success - before asking the question here. — To Do, Aug 01 '17 at 07:20
select productLine, employeeId, salesDate, mon=month(salesdate), yr=year(salesdate), sum(salesTotal) as salesTotal from #tbl_data group by cube (productline, employeeid, salesdate,mon,yr) — Renato Afonso, Aug 01 '17 at 07:31

Lukasz Szozda · Accepted Answer · 2017-08-01T07:40:35.060

6

You could use:

;WITH cte AS (
  select
    productLine, 
    employeeId, 
    salesDate, 
    mon=month(salesdate),
    yr=year(salesdate),
    salesTotal
  from #tbl_data   
)
select
    productLine, 
    employeeId, 
    salesDate, 
    mon,
    yr ,
    sum(salesTotal) as salesTotal
from cte
group by grouping sets
(
     (productLine, employeeId, salesDate, yr)
    ,(productLine, employeeId, yr)
    ,(productLine)
    ,(productLine, mon, yr)
);

Rextester Demo

edited Aug 01 '17 at 07:40

answered Aug 01 '17 at 07:31

Lukasz Szozda

162,964
23
234
275

Thanks for the quick response, Iad2025! It's very close. How do you group the saledates in the same month for an employee under the month? Please refer to my screenshot where the big arrows in red are pointing at. – To Do Aug 01 '17 at 08:22
2

I got it. Just simply add ,(productLine, employeeId, mon) the grouping sets – To Do Aug 01 '17 at 08:32

Grouping sets by year, month, and date

1 Answers1