SUM of MAX (TOP x)

Question

Let's 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 do I get a value from the four highest values, where code = A (i.e. just want to get the sum 300 + 300 + 200 + 75)

thanks

Answer 1

You can use a view or Common Table Expression to get the top 4 and then SUM .

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

If you want a SUM for TOP 4 for each 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