SQL Exercise: Patients with at least one physician appointment

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

4. From the following table, write a SQL query to count the number of patients who scheduled an appointment with at least one physician. Return count as "Number of patients taken at least one appointment".

 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

Sample Solution:

SELECT count(DISTINCT patient) AS "No. of patients taken at least one appointment"
FROM appointment;

Sample Output:

 No. of patients taken at least one appointment
------------------------------------------------
                                              4
(1 row)

Explanation:

The given query in SQL that selects the number of distinct patients who have taken at least one appointment from a table 'appointment'.

The uses of COUNT and DISTINCT functions in combination with the patient column counts the number of unique patients in the table.

The query will return only a single row with a single column.

The AS keyword is used to provide a column alias, which renames the column to "No. of patients taken at least one appointment" for easier readability .

Pictorial presentation:

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: Physicians who is the head of each department.
Next SQL Exercise: Find the floor and block with a given room number.