CODEWITHSUNDEEP
sqloracle-database
Abyss
Abyss

Reputation: 13

RANK over sum of values from multiple columns in SQL

so this is my table:

S_ID | PHY | CHEM | MATHS | BIO |
_____________________________________
S01  | 100 | 100  | 100   | 100 |
S02  | 90  | 90   | 80    |  90 |
S03  | 80  | 76   | 45    |  40 |
S04  | 70  | 91   | 71    |  65 |
S05  | 70  | 99   | 88    |  67 |
S06  | 40  | 56   | 55    |  88 |
S07  | 10  | 55   | 62    |  88 |

I need to add all the marks and then give ranks and percentiles to all the student ids based on the total.

Any help on how to do that ? i am unable to use the RANK() function over the total.. i am using (PHY+CHEM+BIO+MATHS) as TOTAL and then choosing rows where ROWNUM < whatever rank i need. 

SELECT * FROM (
SELECT S_ID, (MATHS+BIO+CHEM+PHY) AS TOTAL FROM MARKS ORDER BY TOTAL DESC )
WHERE ROWNUM < 4;

Upvotes: 1

Views: 2539

Answers (2)

Abyss
Abyss

Reputation: 13

Select S_ID, (MATHS+BIO+CHEM+PHY) AS TOTAL,

   TO_CHAR(PERCENT_RANK() OVER (ORDER BY MATHS+BIO+CHEM+PHY), '9.9999')*100 as PERCENTILE

FROM MARKS

ORDER BY TOTAL DESC;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269933

Of course you can use the rank() function on the total:

select S_ID, (MATHS+BIO+CHEM+PHY) AS TOTAL,
       RANK() OVER (ORDER BY MATHS+BIO+CHEM+PHY DESC) as rank
FROM MARKS
ORDER BY TOTAL DESC;

I'm not sure exactly what you mean by percentile, but the other analytic functions should work similarly.

EDIT: I would do the "percentile" using explicit logic. Something like:

select s.*
from (SELECT S_ID, (MATHS+BIO+CHEM+PHY) AS TOTAL,
             RANK() OVER (ORDER BY MATHS+BIO+CHEM+PHY DESC) as rank,
             COUNT(*) OVER () as num
      FROM MARKS
    ) s
WHERE num * 60/100 <= rank
      ORDER BY TOTAL DESC;

There may be other functions such as that you can also use for this purpose.

Upvotes: 2

Related Questions