Tags

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


SQL Puzzle | Getting group by sum with Total Sum in a Single Query

This puzzle is really cool. You have to get group by sum with Total Sum in a Single query.

Please check out the sample input and the expected output below-

Sample Input
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 COUNT
1 10
2 300
3 300
4 1000
SUM 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 GroupBySumWithTotalSum
(
       ID INT
       ,VAL INT
)
GO


INSERT INTO GroupBySumWithTotalSum 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 GroupBySumWithTotalSum(ID)
GO


--

Solution 1


--

SELECT ISNULL(CAST(Id AS VARCHAR(3)),'Sum') Id, SUM(Val) Val FROM GroupBySumWithTotalSum
GROUP BY Id WITH ROLLUP


--

Solution 2


--

SELECT ISNULL(CAST(Id AS VARCHAR(3)),'Sum') Id, SUM(Val) Val FROM GroupBySumWithTotalSum
GROUP BY Id WITH CUBE

--

Solution 3


--

SELECT CAST(Id AS VARCHAR(3)) Id, SUM(Val) Val FROM GroupBySumWithTotalSum
GROUP BY Id
UNION ALL
SELECT 'Sum', SUM(Val) FROM GroupBySumWithTotalSum

--

Execution Plan / Performance Comparison

Clearly the first & second method are the best one as in the third query we are hitting the table twice and there is a conversion warning also @ the beginning of the third query.

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