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.

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.

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/