SQL Exercise: Patients and doctors who gave them preliminary care.

Last update on December 20 2024 11:41:36 (UTC/GMT +8 hours)

13. From the following tables, write a SQL query to identify the patients and the number of physicians with whom they have scheduled appointments. Return Patient name as "Patient", number of Physicians as "Appointment for No. of Physicians".

 appointmentid |  patient  | prepnurse | physician |    start_dt_time    |     end_dt_time     | examinationroom
---------------+-----------+-----------+-----------+---------------------+---------------------+-----------------
      13216584 | 100000001 |       101 |         1 | 2008-04-24 10:00:00 | 2008-04-24 11:00:00 | A
      26548913 | 100000002 |       101 |         2 | 2008-04-24 10:00:00 | 2008-04-24 11:00:00 | B
      36549879 | 100000001 |       102 |         1 | 2008-04-25 10:00:00 | 2008-04-25 11:00:00 | A
      46846589 | 100000004 |       103 |         4 | 2008-04-25 10:00:00 | 2008-04-25 11:00:00 | B
      59871321 | 100000004 |           |         4 | 2008-04-26 10:00:00 | 2008-04-26 11:00:00 | C
      69879231 | 100000003 |       103 |         2 | 2008-04-26 11:00:00 | 2008-04-26 12:00:00 | C
      76983231 | 100000001 |           |         3 | 2008-04-26 12:00:00 | 2008-04-26 13:00:00 | C
      86213939 | 100000004 |       102 |         9 | 2008-04-27 10:00:00 | 2008-04-21 11:00:00 | A
      93216548 | 100000002 |       101 |         2 | 2008-04-27 10:00:00 | 2008-04-27 11:00:00 | B

    ssn    |       name        |      address       |  phone   | insuranceid | pcp
-----------+-------------------+--------------------+----------+-------------+-----
 100000001 | John Smith        | 42 Foobar Lane     | 555-0256 |    68476213 |   1
 100000002 | Grace Ritchie     | 37 Snafu Drive     | 555-0512 |    36546321 |   2
 100000003 | Random J. Patient | 101 Omgbbq Street  | 555-1204 |    65465421 |   2
 100000004 | Dennis Doe        | 1100 Foobaz Avenue | 555-2048 |    68421879 |   3

Sample Solution:

-- SELECTing patient name and count of appointments for each patient
SELECT p.name AS "Patient",
       COUNT(t.patient) AS "Appointment for No. of Physicians"
-- FROM appointment table aliased as t
FROM appointment t
-- INNER JOIN with patient table aliased as p based on patient's SSN
JOIN patient p ON t.patient = p.ssn
-- GROUP BY patient name
GROUP BY p.name
-- HAVING clause filters out patients with less than one appointment
HAVING COUNT(t.patient) >= 1;

Sample Output:

      Patient      | Appointment for No. of Physicians
-------------------+-----------------------------------
 Grace Ritchie     |                                 2
 John Smith        |                                 3
 Dennis Doe        |                                 3
 Random J. Patient |                                 1
(4 rows)

Explanation:

The said query in SQL that retrieves the number of appointments made by each patient, along with the count of how many physicians each patient has seen.

The table appointment aliases as t and the table patient aliases as p are used to simplify the syntax.

The JOIN statement links the appointment table with the patient table using a matching condition where the patient's SSN matches the appointment's patient ID.

The GROUP BY statement ensures that the count function aggregates the appointment count for each patient, while the HAVING statement applies a condition to include only those patients who have had at least one appointment.

Alternative Solutions:

Using COUNT DISTINCT:

-- SELECTing patient name and count of distinct appointments for each patient
SELECT p.name AS "Patient",
       COUNT(DISTINCT t.appointmentid) AS "Appointment for No. of Physicians"
-- FROM appointment table aliased as t
FROM appointment t
-- INNER JOIN with patient table aliased as p based on patient's SSN
JOIN patient p ON t.patient = p.ssn
-- GROUP BY patient name
GROUP BY p.name;

Explanation:

This solution uses COUNT with DISTINCT to count the number of distinct appointments for each patient. INNER JOIN connects the appointment and patient tables based on patient's SSN. GROUP BY groups the results by patient name, showing the count of distinct appointments.

Using Subquery in SELECT:

-- SELECTing patient name and subquery for the count of appointments for each patient
SELECT p.name AS "Patient",
       (SELECT COUNT(*) FROM appointment t WHERE t.patient = p.ssn) AS "Appointment for No. of Physicians"
-- FROM patient table aliased as p
FROM patient p
-- Ensures only patients with at least one appointment are included
WHERE EXISTS (SELECT 1 FROM appointment t WHERE t.patient = p.ssn);

Explanation:

This solution uses a subquery in the SELECT clause to count the number of appointments for each patient. The main query selects patient names and uses a WHERE EXISTS clause to filter out patients with no appointments.

Practice Online

E R Diagram of Hospital Database:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Find the patients and the physicians who treated them.
Next SQL Exercise: Count unique patients who came to examination room C.