Q:

percentile score, how many people did worse then you did postgresql

I want to calculate how many (percent) of people did worse then I did on my tests.
Here is the result i want to have:
 student | vak   | resultaat | percentielscore 
---------+----------+-----------+-----------------
 1000001 | IBUI  | 5.1 | 0
 1000001 | ILNUX1| 3.4 | 0
 1000001 | IMUML | 6.9 | 0
 1000001 | IRDB  | 2.5 | 0
 1000002 | IARCH | 7.8 | 0
 1000002 | ICOM  | 5.6 | 0
 1000002 | INST  | 6.2 | 0
 1000002 | IRDB  | 7.2 | 100
 1000003 | IARCH | 7.8 | 0
 1000003 | ILNUX1| 7.4 | 33
 1000003 | IMUML | 6.9 | 0
 1000003 | INST  | 6.2 | 0
 1000003 | IRDB  | 3.5 | 25
 1000004 | IBUI  | 9.5 | 100
 1000004 | ICOM  | 5.6 | 0
 1000004 | ILNUX1| 7.4 | 33
 1000004 | IRDB  | 3.5 | 25
 1000005 | ILNUX1| 7.4 | 33
 1000005 | IMHTB | 4.2 | 
 1000005 | IMUML | 6.9 | 0
 1000005 | INST  | 6.2 | 0
 1000005 | IRDB  | 4.8 | 75

I simply dont have a clue how to approach this, googling percentile results always ends up in something like this: 
student | vak   | resultaat | percentielscore 
---------+----------+-----------+-----------------
"1000001";"ILNUX1  ";2.9;  2.40
"1000001";"IMUML   ";6.4;  5.29
"1000001";"IBUI    ";4.6;  3.80
"1000001";"IRDB    ";2.0;  1.65
"1000002";"INST    ";5.7;  4.71
"1000002";"IARCH   ";7.3;  6.03
"1000002";"IRDB    ";6.7;  5.54
"1000002";"ICOM    ";5.1;  4.21
"1000003";"IMUML   ";6.4;  5.29
"1000003";"IRDB    ";3.0;  2.48
"1000003";"INST    ";5.7;  4.71
"1000003";"IARCH   ";7.3;  6.03
"1000003";"ILNUX1  ";6.9;  5.70
"1000004";"IRDB    ";3.0;  2.48
"1000004";"ILNUX1  ";6.9;  5.70
"1000004";"ICOM    ";5.1;  4.21
"1000004";"IBUI    ";9.0;  7.44
"1000005";"IRDB    ";4.3;  3.55
"1000005";"ILNUX1  ";6.9;  5.70
"1000005";"IMUML   ";6.4;  5.29
"1000005";"IMHTB   ";3.7;  3.06
"1000005";"INST    ";5.7;  4.71

any ideas?

A:

You can just use rolling count. So if you want to calculate how many (percent) of people did worse then I did on my tests, use this query:
with cte as (
    select
        student, vak, resultaat, percentielscore,
        count(*) over(partition by vak, resultaat) as result_count,
        count(*) over(partition by vak order by resultaat) as rolling_count,
        count(*) over(partition by vak) as total_count
    from Table1
    order by vak
)
select
    student, vak, resultaat, percentielscore,
    (rolling_count - result_count) * 100.0 / total_count as percentielscore2
from cte
order by student, vak

sql fiddle demo
But results are not exactly the same as your results, because from my point of view, your results are not showing how many (percent) of people did worse then I did on my tests, check, for example, ILNUX1 test - there're 4 people, 3 of these have 7.4 and one has 3.4, how percentilscore could be 33?

