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)
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.
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.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With