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 🙂

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/