SQL Puzzle | Getting group by sum and total sum in a single query

This puzzle is really cool. You have to get group by sum and total sum in a single query. Please check out the sample input and the expected output below-

Sample Input

Expected Output

Rules/Restrictions

• The solution should be should use “SELECT” statement or a CTE.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table & insert some sample data

--



CREATE TABLE GroupBySumTotalSum
(
	 ID INT
	,VAL INT
)
GO


INSERT INTO GroupBySumTotalSum VALUES
(1,	10),
(1,	NULL),
(2,	NULL),
(2,	100),
(2,	200),
(3,	200),
(3,	100),
(4,	156),
(4,	255),
(4,	244),
(4,	NULL),
(4,	NULL),
(4,	345)
GO

CREATE CLUSTERED INDEX Ix_ID ON GroupBySumTotalSum(ID)
GO


--

Solution 1

--

SELECT 
       ID, SUM(Val) GroupBySum, SUM(SUM(Val)) OVER() TotalSum
FROM GroupBySumTotalSum
GROUP BY ID

--

Solution 2

--

SELECT DISTINCT ID, SUM(Val) OVER (PARTITION BY ID) GroupBySum, SUM(Val) OVER() TotalSum
FROM GroupBySumTotalSum

--

Execution Plan / Performance Comparison

Execution Plans – Getting group by sum and total sum in a single query [Multiple Solutions – Best One]

Clearly the first method wins here. In second query we are doing an explicit sort on all the columns we get in the select list. Now this sort will be very expensive as we cannot have indexes on the columns we are sorting.

Add a comment if you have any other or better 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.

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