Tags

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


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

Advertisements