SUM of MAX(TOP x)

Question

Say I have a table like the following:

PK Code Value
1  A    200
2  A    300
3  A    25
4  A    75
5  A    50
6  A    15
7  A    300
8  A    75

How would I get the value of the top 4 highest values where code=A (i.e. just want the sum of 300 + 300 + 200 + 75)

Thanks

Answer 1

You can use a derived table or Common Table Expression to get the top 4 then SUM that.

SELECT SUM(Value) As Top4Sum
FROM
(
    SELECT TOP (4) Value
    FROM YourTable
    WHERE Code = 'A'
    ORDER BY Value DESC
) T

If you wanted the SUM of the TOP 4 for every Code you could do

;WITH CTE
     AS (SELECT *,
                ROW_NUMBER() OVER (PARTITION BY Code ORDER BY Value DESC) RN
         FROM   YourTable)
SELECT Code,
       SUM(Value)
FROM   CTE
WHERE  RN <= 4
GROUP  BY Code