Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Grouping sets by year, month, and date

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)

enter image description here

like image 280
To Do Avatar asked Aug 01 '17 06:08

To Do


People also ask

How do you use grouping sets?

GROUPING SET is an extension of the GROUP BY clause. The GROUP BY statement is GROUPING SET is an extension of the GROUP BY clause. The GROUP BY statement is used to summarize the data in conjunction with aggregate functions such as SUM, AVG, COUNT, etc. It groups the result set based on the single or multiple columns.

What are grouping sets?

GROUPING SETS are groups, or sets, of columns by which rows can be grouped together. Instead of writing multiple queries and combining the results with a UNION, you can simply use GROUPING SETS. GROUPING SETS in SQL can be considered an extension of the GROUP BY clause.

How do you group dates into years in SQL?

You simply use the aggregate function (here: SUM ) with the correct column and at the end of the query you group by year . You can rename the column using the AS keyword with a new name.

How do I select month and year from date in SQL?

To get the year and the month columns, use the EXTRACT(part FROM date) function. In this solution, the part argument is replaced by YEAR and MONTH to get the year and the month separately, each in its own column. You can learn more about EXTRACT() in the official MySQL documentation.


1 Answers

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

like image 142
Lukasz Szozda Avatar answered Nov 02 '22 09:11

Lukasz Szozda