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 2 value rows and a third with the sum of others?
Something like this:
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
In the assumption you are using SQL Server 2005 or higher, using a CTE would do the trick.
ROW_NUMBER
to each row, starting with the highest value, resetting for each month.SELECT
the 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's go to Andriy for teaching me some new tricks.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With