CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.3rank
Tito
Tito

Reputation: 671

How to rank top categories in a column postgres

I need to find the top 5 nationalities in a school.

Select count(distinct studentnr),
       count(case when ctf.text like 'United Kingdom' then 1 end) as nation1,
       count(case when ctf.text like 'Germany' then 1 end) as nation2,
       count(case when ctf.text like 'France' then 1 end) as nation3,
       count(case when ctf.text like 'Italy' then 1 end) as nation4,
       count(case when ctf.text like 'Hungary' then 1 end) as nation5

from student s  
       join pupil p on p.id = s.personid
       join pupilnationality pn on pn.pupilid = p.id
       join country ctf on ctf.id = pn.countryid

As you see this is a manual search, I want it that I look up the fields and make a count and categorize them seperately in a column.

However I only want top 5 Here is pretty much what I want Does this require partition or rank?

enter image description here

Upvotes: 0

Views: 85

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

Why not put them in separate rows?

select ctf.text, count(*)
from student s join
     pupil p
     on p.id = s.personid join
     pupilnationality pn
     on pn.pupilid = p.id join
     country ctf
     on ctf.id = pn.countryid
group by ctf.text
order by count(*) desc
limit 5;

Upvotes: 1

Related Questions