SQL SERVER | Total and Group By Total Puzzle or The Grouping Sets Puzzle

In this puzzle you have to find the SUM of vals1, Vals2 and vals3 column for each Id and Vals column and you need show that after each group. For more details please refer sample input and expected output

Sample INPUT

Id Vals Vals1 Vals2 Vals3
A S1 103 53 3
A S2 45 31 12
A S3 10 21 23
B S3 67 19 8
B S4 20 22 1
B S5 20 1 25

Expected OUPUT

Title Id Vals Vals1 Vals2 Vals3
A S1 103 53 3
A S2 45 31 12
A S3 10 21 23
SubGroup Total A NULL 158 105 38
B S3 67 19 8
B S4 20 22 1
B S5 20 1 25
SubGroup Total B NULL 107 42 34
Total NULL NULL 265 147 72

Use below script to create table and insert sample data into it.

 ```-- CREATE TABLE GroupsAndTotalData ( Id VARCHAR(10), Vals VARCHAR(50), Vals1 INT, Vals2 INT, Vals3 INT ) GO INSERT INTO GroupsAndTotalData VALUES ('A','S1',103,53,3), ('A','S2',45,31,12), ('A','S3',10,21,23), ('B','S3',67,19,8) , ('B','S4',20,22,1) , ('B','S5',20,1,25) GO SELECT * FROM GroupsAndTotalData GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solutions 🙂

SOLUTION 1

 ```-- SELECT CASE WHEN GROUPING_ID(Id,Vals) = 3 THEN 'Total' WHEN GROUPING_ID(Id,Vals) = 1 THEN 'SubGroup Total' WHEN GROUPING_ID(Id,Vals) = 0 THEN '' END Title, Id ,Vals ,SUM(Vals1) Vals1 ,SUM(Vals2) Vals2 ,SUM(Vals3) Vals3 FROM GroupsAndTotalData GROUP BY GROUPING SETS ( (Id,Vals), (Id), () ) -- ```

OUTPUT 1

 ```-- Title Id Vals Vals1 Vals2 Vals3 -------------- ---------- -------------- ----------- ----------- ----------- A S1 103 53 3 A S2 45 31 12 A S3 10 21 23 SubGroup Total A NULL 158 105 38 B S3 67 19 8 B S4 20 22 1 B S5 20 1 25 SubGroup Total B NULL 107 42 34 Total NULL NULL 265 147 72 (9 rows affected) -- ```

References

Add a comment if you have any solution in mind. I would love to learn it. We all need to learn.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/