CODEWITHSUNDEEP
sqlsql-servergroup-bysum
Mataunited18
Mataunited18

Reputation: 628

Query in SQL to sum the top nth largest of a column

I have two columns I care about YearMonth and Value.

How can I sum the 50th (1st to 50th, i.e. 1st, 2nd, ..., 50th) largest values from Value and create a new column based on the criteria?

I am able to do SUM(CASE WHEN Value > 100 THEN Value END) AS LargeValue but the 1st to 50th largest value change every YearMonth, so doing this wouldn't be completely right.

I couldn't find any solutions for this, and most replies finds rather than the sum the n'th largest.

The table looks like this (simplified):

====================
YearMonth Value Flow
====================
201801   500   10
201801   400   -5
 ...     ...   ...
201802   700   20
201802   100  -20 
201802   50    10

Upvotes: 1

Views: 474

Answers (3)

Zsolt Botykai
Zsolt Botykai

Reputation: 51603

SELECT t.*, 
       SUM(CASE WHEN t.r < 51 THEN t.value ELSE 0 END) OVER() runningtotal 
  FROM (
         SELECT a.yearmonth, 
                a.value, 
                RANK() OVER (ORDER BY value DESC) r 
           FROM yourtable a ) t 
 ORDER BY t.r asc

Firts creating a ranked table order than creating a runningtotal column for the ranked table only for the first 50 elements.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269743

You can use window functions:

select t.*,
       sum(case when seqnum <= 50 then value end) over
           (partition by yearmonth) as top50_sum
from (select t.*,
             row_number() over (partition by yearmonth order by value desc) as seqnum
      from t
     ) t;

If you just want one row per yearmonth in the result set, then use group by:

select t.yearmonth, sum(value) as top50_sum
from (select t.*,
             row_number() over (partition by yearmonth order by value desc) as seqnum
      from t
     ) t
where seqnum <= 50
group by yearmonth

Upvotes: 2

sticky bit
sticky bit

Reputation: 37472

You can used a derived table ordering the row by value descending and limiting the result to 50 rows. Then take the sum from that.

How the limiting is written depends on the DBMS. In many of them LIMIT will work.

SELECT sum(x.value)
       FROM (SELECT t.value
                    FROM elbat t
                    ORDER BY t.value DESC
                    LIMIT 50) x;

Upvotes: 0

Related Questions