Tags

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


SQL PUZZLE | SUM EVERY 3 rows for each Id Puzzle | SQL Interview Question

In this puzzle you have to get SUM of every 3 rows of Val column for each Id

1 : First 3 rows should be combined together, then next 3 rows.
2 : If you have less then 3 rows then also we need to go ahead and combine the rows.

For more details please refer sample input and expected output.

Sample Input

Id Nums Val
1 101 10
1 102 5
1 103 10
1 104 20
2 105 25
2 106 10
2 107 15
2 108 45
2 109 5
3 110 0
3 111 10
3 112 20
3 113 80
3 114 20
3 115 5
4 116 10

Expected output

Id Nums Val 3Sums
1 101 10 25
1 102 5 25
1 103 10 25
1 104 20 20
2 105 25 50
2 106 10 50
2 107 15 50
2 108 45 50
2 109 5 50
3 110 0 30
3 111 10 30
3 112 20 30
3 113 80 105
3 114 20 105
3 115 5 105
4 116 10 10

Script – DDL and INSERT sample data

--

CREATE TABLE TheThreeGroups
(
	 Id INT
	,Nums INT
	,Val INT
)
GO

INSERT INTO TheThreeGroups VALUES
(1,101,10),
(1,102,5),
(1,103,10),
(1,104,20),
(2,105,25),
(2,106,10),
(2,107,15),
(2,108,45),
(2,109,5),
(3,110,0),
(3,111,10),
(3,112,20),
(3,113,80),
(3,114,20),
(3,115,5),
(4,116,10)
GO

SELECT * FROM TheThreeGroups
GO


--

SOLUTION 1 – USING SUM AND ROW_NUMBER

--

SELECT Id, Nums, Val , SUM(Val) OVER(PARTITION BY Id,rnk) [3Sums] FROM 
(
	SELECT * , (ROW_NUMBER() OVER(PARTITION BY Id ORDER BY Nums) - 1) / 3 rnk FROM TheThreeGroups
)k
GO

--

OUTPUT 1

--

Id          Nums        Val         3Sums
----------- ----------- ----------- -----------
1           101         10          25
1           102         5           25
1           103         10          25
1           104         20          20
2           105         25          50
2           106         10          50
2           107         15          50
2           108         45          50
2           109         5           50
3           110         0           30
3           111         10          30
3           112         20          30
3           113         80          105
3           114         20          105
3           115         5           105
4           116         10          10

(16 rows affected)

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

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