Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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

# URL
1 https://technet.microsoft.com/en-us/library/bb510427(v=sql.105).aspx
2 https://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx
3 https://blogs.msdn.microsoft.com/craigfr/2007/10/11/grouping-sets-in-sql-server-2008/

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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 Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ 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/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

Advertisements