and GROUPING SETS | Improving my SQL BI Skills

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

Improving my SQL BI Skills

Tag Archives: and GROUPING SETS

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

Share this