T-sql by category and get top n values

Question

Imagine I have this table:

Month | Person | Value ---------------------- Jan | P1 | 1 Jan | P2 | 2 Jan | P3 | 3 Feb | P1 | 5 Feb | P2 | 4 Feb | P3 | 3 Feb | P4 | 2 ... 

How can I build a t-sql query to get the top two rows of values ​​and the third with the sum of the others?

Something like that:

 RESULT: Month | Person | Value ---------------------- Jan | P3 | 3 Jan | P2 | 2 Jan | Others | 1 -(sum of the bottom value - in this case (Jan, P1, 1)) Feb | P1 | 5 Feb | P2 | 4 Feb | Others | 5 -(sum of the bottom values - in this case (Feb, P3, 3) and (Feb, P4, 2)) 

thanks

Answer 1

Assuming you are using SQL Server 2005 or higher, using CTE will do the trick.

• Attach ROW_NUMBER to each row, starting with the highest value, resetting for each month.
• SELECT top 2 rows for each month from this query (rownumber <= 2)
• UNION with the remaining rows (rownumber> 2)

SQL statement

 ;WITH Months (Month, Person, Value) AS ( SELECT 'Jan', 'P1', 1 UNION ALL SELECT 'Jan', 'P2', 2 UNION ALL SELECT 'Jan', 'P3', 3 UNION ALL SELECT 'Feb', 'P1', 5 UNION ALL SELECT 'Feb', 'P2', 4 UNION ALL SELECT 'Feb', 'P3', 3 UNION ALL SELECT 'Feb', 'P4', 2 ), q AS ( SELECT Month , Person , Value , RowNumber = ROW_NUMBER() OVER (PARTITION BY Month ORDER BY Value DESC) FROM Months ) SELECT Month , Person , Value FROM ( SELECT Month , Person , Value , RowNumber FROM q WHERE RowNumber <= 2 UNION ALL SELECT Month , Person = 'Others' , SUM(Value) , MAX(RowNumber) FROM q WHERE RowNumber > 2 GROUP BY Month ) q ORDER BY Month DESC , RowNumber 

Kudo go to Andrey to teach me new tricks.

Answer 2

 ;WITH atable (Month, Person, Value) AS ( SELECT 'Jan', 'P1', 1 UNION ALL SELECT 'Jan', 'P2', 2 UNION ALL SELECT 'Jan', 'P3', 3 UNION ALL SELECT 'Feb', 'P1', 5 UNION ALL SELECT 'Feb', 'P2', 4 UNION ALL SELECT 'Feb', 'P3', 3 UNION ALL SELECT 'Feb', 'P4', 2 ), numbered AS ( SELECT Month, Person, Value, rownum = ROW_NUMBER() OVER (PARTITION BY Month ORDER BY Value DESC) FROM atable ), grouped AS ( SELECT Month, Person, Value, Grp = CASE WHEN rownum < 3 THEN rownum ELSE 3 END FROM numbered ) SELECT Month, Person = CASE Grp WHEN 3 THEN 'Others' ELSE MAX(Person) END, Value = SUM(Value) FROM grouped GROUP BY Month, Grp ORDER BY Month DESC, Grp 

Answer 3

 WITH NTable AS ( SELECT [Month], Person, Value, ROW_NUMBER() OVER (PARTITION BY [Month] ORDER BY Value DESC) AS Rownumber FROM MyTable ) SELECT t.[Month], CASE Rownumber WHEN 1 THEN t.Person WHEN 2 THEN t.Person ELSE 'Others' END As Person, SUM(t.Value) As [Sum] FROM NTable t GROUP BY t.[Month], CASE Rownumber WHEN 1 THEN t.Person WHEN 2 THEN t.Person ELSE 'Others' END ORDER BY t.[Month]