CODEWITHSUNDEEP
sql
tuanptit
tuanptit

Reputation: 413

SQL - How to get the count of each distinct value?

I have 3 table

 **room**
    room_id | nurse_needed
    ----------------------
    1       | 2
    2       | 3
    3       | 1

   **doctor_schedule**
    doctor_schedule_id| room_id
    ---------------------------
           1          | 1
           2          | 2
           3          | 3

 *nurse_schedule*
    nurse_schedule_id | doctor_schedule_id
    --------------------------------------
    1                 |         1         
    2                 |         1         
    3                 |         2

Each Room needs a number of nurse, A doctor work in Room and a nurse work with doctor's schedule. I want to count how many nurse in each room. The result should be: 

room_id     | nurse_needed|nurse_have_in_room
---------------------------------------------
    1       | 2           | 2
    2       | 3           | 1
    3       | 1           | 0

Upvotes: 0

Views: 778

Answers (3)

JBrooks
JBrooks

Reputation: 10013

select r.room_id, 
r.nurse_needed, 
ns.nurses_scheduled,
ns.dist_nurses_scheduled
from room r
left join (select ds.room_id, 
           count(1) nurses_schedule,
           count(distinct ns.nurse_schedule_id) dist_nurses_scheduled
           from doctor_schedule ds
           join nurse_schedule ns
           on ds.doctor_schedule_id  = ns.doctor_schedule_id 
           group by ds.room_id) as ns
on r.room_id = ns.room_id

Left join so you find rooms with no nurses scheduled.
Count(distinct ns.nurse_schedule_id) if needed to see how many different nurses make up the count.

Normally you have a time component in there too. Something like "where r.roomdate = ns.date"

Upvotes: 1

user2736738
user2736738

Reputation: 30926

select room.*,
(select count(*) from 
dotor_schedule docs,
nurse_schedule nurs
where docs.doctor_schedule_id=nurs.dcotor_schedule_id 
   group by docs.room_id) as nurse_have_in_room
from room;

 Result of join on doctor_schedule_id between doctor_schedule and  
 nurse_schedule

nurse_schedule_id | doctor_schedule_id   room_id
--------------------------------------+------------
1                 |         1         |    1
2                 |         1         |    1
3                 |         2         |    2

We group by room_id and then get the result.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Hmmm . . .

select r.*,
       (select count(*)
        from doctor_schedule ds join
             nurse_schedule ns
             on ds.doctor_schedule_id = ns.doctor_schedule_id
        where ds.room_id = r.room_id
       ) as nurse_have_in_room
from room r;

Upvotes: 3

Related Questions