Greetings,
I need to transform data returned in a query from several tables as shown below to the desired output shown below. This looks like some type of rollup and I have tried Grouping by State and Department but I can't get it to display the sums as shown. I have to include the non aggregated data in the Group by and I think this is throwing this off. I looked at using Grouping Sets but that didn't work for me either. Any idea of the correct approach to restructure this data into something approximating what is shown below. The intent is to display the raw groups of data by State and Department and sum the financial info by Group and State below the raw data and display a Grand Summary below. I've also tried using a Union all approach with some success but I suspect Rollup is my best approach. Another option I looked at was defining variables and populating all of the sums into a temp table in a SP and pulling the data out from there. That seems to work bit is a very bulky way to go about it. Any direction or advice would be appreciated.
State Dept ID Client Date Retail Base Tax
NY A 10 Joe D 1/10/2024 71.44 68.04 3.4
CA B 15 Paul D 2/23/2024 43.46 40 3.46
NY A 14 Jim R 3/14/2024 51.45 49 2.45
NY B 12 Al J 2/13/2024 38.85 37 1.85
NY B 18 Jay H 2/15.2024 82.60 80 2.60
CA B 16 Pete Y 2/18/2024 65.4 64 1.40
CA A 20 Joe P 2/11/2024 73.5 70 3.5
Desired Output
State Dept ID Client Date Retail Base Tax
NY A 10 Joe D 1/10/2024 71.44 68.04 3.4
NY A 14 Jim R 3/14/2024 51.45 49 2.45
NY A 122.89 117.04 5.85
NY B 12 Al J 2/13/2024 38.85 37 1.85
NY B 18 Jay H 2/15.2024 82.60 80 2.60
NY B 121.45 117.0 4.45
State Summary: NY
Sum (Retail) 244.34
Sum (Base) 234.04
Sum (Tax) 10.30
CA A 20 Joe P 2/11/2024 73.5 70 3.5
CA A 73.5 70 3.5
CA B 15 Paul D 2/23/2024 43.46 40 3.46
CA B 16 Pete Y 2/18/2024 65.4 64 1.40
CA B 108.86 104 4.86
State Summary CA
Sum (Retail) 182.36
Sum (Base) 174
Sum (Tax) 8.36
Grand Summary:
Sum (Retail) 426.7
Sum (Base) 408.04
Sum (Tax) 18.66
May 14, 2024 at 8:08 am
Please provide your sample data in consumable format (in the form of CREATE TABLE/INSERT statements).
If the format of your desired output is exactly as stated, you would be better off using a reporting tool to produce it. Any straight T-SQL solution is limited by the fact that the number of columns and their datatypes is fixed in the final output.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
This is about as close as you can get using SQL. If you want more control over your formatting, you should use a reporting tool.
SELECT CASE WHEN GROUPING(v.Dept) = 0 THEN v.[State]
WHEN GROUPING(v.[State]) = 0 THEN CONCAT('State Summary: ', v.[State])
ELSE 'Grand Summary:'
END AS [State]
, v.Dept
, v.ID
, v.Client
, v.[Date]
, SUM(Retail) AS Retail
, SUM(Base) AS Base
, SUM(Tax) AS Tax
FROM
( -- Use your table here
VALUES ('NY', 'A', 10, 'Joe D', '1/10/2024', 71.44, 68.04, 3.4)
, ('CA', 'B', 15, 'Paul D', '2/23/2024', 43.46, 40, 3.46)
, ('NY', 'A', 14, 'Jim R', '3/14/2024', 51.45, 49, 2.45)
, ('NY', 'B', 12, 'Al J', '2/13/2024', 38.85, 37, 1.85)
, ('NY', 'B', 18, 'Jay H', '2/15.2024', 82.60, 80, 2.60)
, ('CA', 'B', 16, 'Pete Y', '2/18/2024', 65.4, 64, 1.40)
, ('CA', 'A', 20, 'Joe P', '2/11/2024', 73.5, 70, 3.5)
) v([State], Dept, ID, Client, [Date], Retail, Base, Tax)
GROUP BY GROUPING SETS((), (v.[State]), (v.[State], v.Dept), (v.[State], v.Dept, v.ID, v.Client, v.[Date]))
ORDER BY GROUPING(v.[State]), v.[State] DESC, GROUPING(v.Dept), v.Dept, GROUPING(v.ID), v.ID
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 15, 2024 at 2:22 am
This is exactly what I was trying to do. Thanks very much. The grouping was done by a CRM app but it also nested the content and the formatting was garbled when exported to Excel so we needed to rebuild it. I learned some new tricks with grouping sets
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply