July 20, 2018 at 2:18 am
Hi
I have the following table :
Date Id Measure Sector Value
2018-08-10 1 Current Month Branded 87079513
2018-08-10 2 Current Quarter Branded 287129035
2018-08-10 3 Year Remaining Branded 587004351
2018-08-10 1 Current Month Retail 51007903
2018-08-10 2 Current Quarter Retail 176363292
2018-08-10 3 Year Remaining Retail 12119429
I'd like to add some total rows by Sector for the matching Measure eg. the total for Branded and Retail for Current Month. I've played around with ROLLUP and GROUPING SETS but can't seem to get the right solution. Can anyone shed any light on where to begin?
Thanks
July 20, 2018 at 9:44 am
For someone who's been around as long as you have, you should know that's not a table. If people can't recreate your scenario, you're not likely to get an answer. Here's a table:CREATE TABLE #TestData (
MeasureDate DATE,
ID int,
Sector VARCHAR(30),
Amount money );
GO
INSERT INTO #TestData VALUES
('2018-08-10',1, 'Current Month Branded', 87079513)
,('2018-08-10',2, 'Current Quarter Branded', 287129035)
,('2018-08-10',3, 'Year Remaining Branded', 587004351)
,('2018-08-10',1, 'Current Month Retail', 51007903)
,('2018-08-10',2, 'Current Quarter Retail', 176363292)
,('2018-08-10',3, 'Year Remaining Retail', 12119429);
Then make a crosstab using this article http://www.sqlservercentral.com/articles/T-SQL/63681/
Could you post your expected output? Just makes it easier to connect the dots.
July 30, 2018 at 3:17 am
Hi
You're absolutely right, that wasn't a table. My bad! Expanding on your table (thanks) I used grouping sets to get the following....
select ID, MeasureDate, Sector, sum(Amount) Amount from #TestData where Id = 1
group by GROUPING SETS((ID, MeasureDate, Sector),())
Union ALL
select ID, MeasureDate, Sector, sum(Amount) Amount from #TestData where Id = 2
group by GROUPING SETS((ID, MeasureDate, Sector),())
Union All
select ID, MeasureDate, Sector, sum(Amount) Amount from #TestData where Id = 3
group by GROUPING SETS((ID, MeasureDate, Sector),())
...however, it caused me issues when trying to sort/group the data in excel/power bi table as the ID, date, sector values are dropped from the totals rows. I then added in hard-coded values which works ok but it really doesn't feel like "best practice"
select 1 as ID, MeasureDate, Sector, sum(Amount) Amount from #TestData where Id = 1
group by GROUPING SETS((ID, MeasureDate, Sector),())
Union ALL
select 2 as ID, MeasureDate, Sector, sum(Amount) Amount from #TestData where Id = 2
group by GROUPING SETS((ID, MeasureDate, Sector),())
Union All
select 3 as ID, MeasureDate, Sector, sum(Amount) Amount from #TestData where Id = 3
group by GROUPING SETS((ID, MeasureDate, Sector),())
Any thoughts?
Thanks
July 30, 2018 at 6:29 am
I'd get rid of the union alls entirely, they're defeating the purpose of using Grouping Sets, and just have WHERE ID IN (1,2,3). You may also want the GROUP BY to be GROUPING SETS((ID, MeasureDatw, Sector),(ID)) if you don't need a complete aggregate and always want things split out by ID.
You can use GROUPING or GROUPING_ID to figure out which belongs to which grouping.
July 31, 2018 at 1:41 am
Thanks Andy, that worked a treat.
Final query is :
select ID, MeasureDate, Sector, sum(Amount) Amount from #TestData where Id in(1,2,3)
group by GROUPING SETS((ID, MeasureDate, Sector),(ID))
I also found this post useful in understanding how grouping sets work (and why not to use UNION ALL 🙂 ) https://www.sqlpassion.at/archive/2014/09/15/the-power-of-grouping-sets-in-sql-server/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply