CODEWITHSUNDEEP
mysqlsqlselectcount
tuanptit
tuanptit

Reputation: 413

SQL - Get the count of each distinct value with condition?

I have three tables

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


**doctor_schedule**
    doctor_schedule_id| room_id | date     |shift
    -------------------------------------------------
           1          | 1       |12-30-2016|   1
           2          | 2       |12-31-2016|   2
           3          | 3       |12-30-2016|   2
           4          | 2       |12-30-2016|   2
*nurse_schedule*
    nurse_schedule_id | doctor_schedule_id
    --------------------------------------
    1                 |         1         
    2                 |         1         
    3                 |         3

Each shift in room have one doctor and in each shift need a number of nurse.Nurses work with doctor's schedule. I want to count how many shift don't have enough nurse in 12-30-2016. The result should be:

    doctor_schedule_id| room_id | date     |shift | nurse_needed|nurse_have_in_room
   ------------------------------------------------------------------------------
           1          | 1       |12-30-2016|   1  |     3       |    2
           3          | 3       |12-30-2016|   2  |     2       |    1
           4          | 2       |12-30-2016|   2  |     1       |    0

Upvotes: 0

Views: 1033

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

You can INNER JOIN all the table like this and filter away the ones where required number of nurses is less than or equal to number of them already present.

select d.doctor_schedule_id,
    d.room_id,
    d.date,
    d.shift,
    r.nurse_needed,
    COUNT(distinct n.nurse_schedule_id) nurse_have_in_room
from doctor_schedule d
inner join room r on r.room_id = d.room_id
left outer join nurse_schedule n on d.doctor_schedule_id = n.doctor_schedule_id
where d.date = '12-30-2016'
group by d.doctor_schedule_id,
    d.room_id,
    d.date,
    d.shift,
    r.nurse_needed
having r.nurse_needed > COUNT(distinct n.nurse_schedule_id);

Upvotes: 1

Mureinik
Mureinik

Reputation: 311188

You could join doctor_schedule with room and an aggregate query on nurse_schedule:

SELECT d.*, r.nurse_needed, n.nurses_in_room
FROM   doctor_schedule d
JOIN   room r ON d.room_id = r.room_id
JOIN   (SELECT   doctor_schedule_id, COUNT(*) AS nurses_in_room
        FROM     nurse_schedule
        GROUP BY doctor_schedule_id) n ON 
       d.doctor_schedule_id = n.doctor_schedule_id AND
       r.nurse_needed > n.nurses_in_room

Upvotes: 1

Related Questions