Tags

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


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

ID Val
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

Expected Output

ID GroupBySum TotalSum
1 10 1610
2 300 1610
3 300 1610
4 1000 1610

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]

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 solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements