SELECT specific row (row on 80% place of number of all rows)

Question

I have some query which returns list of IDs and their SUM for specific column.

select TICKETID,SUM(IMPACT) AS S
from incident
GROUP BY TICKETID
ORDER BY S

Result is for example like this:

TICKETID    S
INC491943   10
INC491944   20
INC491945   30
INC491946   40
INC491950   50

So because this result has 5 rows I need to have as result value from the row which is on 80% place of all rows.

So this will be the row which has value 40 because it has 5 rows and 80% of 5 is number 4 (forth row in this case). So final result is just value 40.

Of course if 80% of all rows result is for example 4.2 then we retrieve 4th row and if result is 4.7 then we retrieve 5th row.

Is this possible at all and what will be the solution? Thanks

Answer 1

Hmmm. I'm inclined to do this:

SELECT i.*
FROM (SELECT TICKETID, SUM(IMPACT) AS S,
             SUM(SUM(IMPACT)) OVER () as total_s,
             SUM(SUM(IMPACT)) OVER (ORDER BY SUM(IMPACT)) as cumulative_s
      FROM incident
      GROUP BY TICKETID
     ) i
WHERE cumulative_s >= 0.8 * total_s AND
      (cumulative_s - s) < 0.8 * total_s;

The above does the calculation by "s". If you just want it on rows:

SELECT i.*
FROM (SELECT TICKETID, SUM(IMPACT) AS S,
             COUNT(*) OVER () as cnt,
             ROW_NUMBER() OVER (ORDER BY SUM(IMPACT)) as seqnum
      FROM incident
      GROUP BY TICKETID
     ) i
WHERE seqnum >= 0.8 * cnt AND
      (seqnum - 1) < 0.8 * cnt;