CODEWITHSUNDEEP
sqlpostgresql
eugene
eugene

Reputation: 41685

sql, group by , count and max

Suppose a hospital has many doctors.

I want to find out the doctor who treated the most patient each day for a given period.

A hypothetical output would be something like

day    doctor    patient-count
1        A            2
2        B            3

At day 1, doctor A treated the most patient and the patient count was 2.

We have data such as the following

day    doctor    patient
1        A         P0
1        B         P1
1        A         P2
2        A         P3
2        B         P4
2        B         P5
2        B         P6

Most examples I found only look for a single row with the most patient count. This is different in that I need multiple rows (for each group)

I'm using postgresql, if that matters.

Upvotes: 0

Views: 130

Answers (1)

jarlh
jarlh

Reputation: 44776

Create a common table expression (cte), that counts each doctors number of daily patients. Use that one and verify max number of patients.

Will return both doctors when it's a tie.

with cte as
(
select day, doctor, count(distinct patient) as patcnt
from tablename
group by day, doctor
)
select day, doctor
from cte t1
where patcnt = (select max(patcnt) from cte t2 where t2.day = t1.day)

Note: ANSI SQL has day as a reserved word. So you may need to delimit it as "day".

Alternatively, do a cte join:

with cte as
(
select day, doctor, count(distinct patient) as patcnt
from tablename
group by day, doctor
)
select t1.day, t1.doctor, t1.patcnt
from cte t1
  join (select day, max(patcnt) as patcnt from cte) t2
    on t1.day = t2.day and t1.patcnt = t2.patcnt

Upvotes: 1

Related Questions