SQL Puzzle | The Sum Puzzle

Puzzle Statement

I came to this puzzle while I was working on a story. Here you have to sum up value of cost column based on values of Id. For Quantity if values are different then we have add those values.

Please go through the sample input and expected output for details.

Sample Input

Id	Cost	Quantity
1234	12	164
1234	13	164
1235	100	130
1235	100	135
1236	12	136

Expected Output

Id	Cost	Quantity
1234	25	164
1235	200	265
1236	12	136

Rules/Restrictions – The solution should be should use “SELECT” statement or “CTE”.

Script – Use below script to create table and insert sample data into it.

--


CREATE TABLE TheSumPuzzle
(
	 ID INT
	,Cost INT
	,Quantity INT
)
GO

INSERT INTO TheSumPuzzle
VALUES
(1234,12,164),
(1234,13,164),
(1235,100,130),
(1235,100,135),
(1236,12,136)
GO

--

SOLUTION 1 | Using SUM function

--


SELECT ID Id ,SUM(COST) Cost,SUM(DISTINCT Quantity) Quantity FROM TheSumPuzzle
GROUP BY Id

--

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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