How to count number of rows in multiple tables in a Database.? RRS feed

  • Question

  • Hi SQL Experts,
    I have needed to count number of staff, number of patients and number of consultants in a ward and my codes don't work. I have attached a picture that shows how my database looks like, the following is my codes, please help.

    SELECT ward.Ward_no, count (ward.In_charge) AS Number_of_Staff,count (distinct Patient_ward.patient_id) AS Number_of_patient,count(distinct Patient_ward.consultant_no) AS Number_of_consultants
    FROM  staff full join Staff_Duty
    on staff.staff_no = staff_duty.staff_no
    full join ward
    on staff_duty.ward_no = ward.ward_no
    left outer join Patient_ward
    on ward.ward_no = patient_ward.ward_no
    group by ward.Ward_no
    order by Number_of_consultants DESC;

    Saturday, August 5, 2017 7:10 AM

All replies

  • Hi,
    Try this:
    SELECT  t1.Ward_no ,
            ISNULL(t3.Number_of_Staff, 0) AS Number_of_Staff ,
            ISNULL(t2.Number_of_consultants, 0) AS Number_of_consultants ,
            ISNULL(t2.Number_of_patient, 0) AS Number_of_patient
    FROM    ward AS t1
            LEFT JOIN ( SELECT  ward_no ,
                                COUNT(DISTINCT consultant_no) AS Number_of_consultants ,
                                COUNT(*) AS Number_of_patient
                        FROM    Patient_ward
                        GROUP BY ward_no
                      ) AS t2 ON t1.ward_no = t2.ward_no
            LEFT JOIN ( SELECT  Ward_no ,
                                COUNT(*) AS Number_of_Staff
                        FROM    Staff_Duty
                        GROUP BY Ward_no
                      ) AS t3 ON t3.Ward_no = t1.Ward_no
    ORDER BY Number_of_consultants DESC;

    Roy Wu(吳熹Blog)(微博)

    Saturday, August 5, 2017 9:05 AM