SQL in Healthcare: Exploring HAVING, GROUP BY, and ORDER BY clauses

Structured Query Language (SQL) is an extensively used powerful language that aids data manipulation with the relational database. It encompasses various commands for data retrieval, modification, filtering, sorting, aggregation, and analysis. Each of these commands serves a distinct purpose in working with databases. Data manipulation is vital in obtaining valuable insights from the available data by making it more organized, which aids in analysis and decision-making.

In particular, ORDER BY, HAVING, and GROUP BY are three distinct clauses in SQL that serve different purposes in querying and manipulating data in a database and can be confusing. Let’s break down the differences between them.

Let’s consider a Healthcare Management System database. This dataset contains patient records, doctor appointments, and medical treatments. The SQL tables will look like these —

Table Name: Patients

Table Name: Appointments

Table Name: Doctors

1. The GROUP BY clause

The GROUP BY clause is used to aggregate the rows in the table in accordance with the specified value. This command usually uses aggregate functions such as average, sum, min, max, etc. All the outcomes derived from these processes are consolidated into a distinct table.

THE GROUP BY clause gives a lens into perusing data at different granular levels that aid in uncovering patterns and trends to obtain insights.

Query

SELECT specialty, COUNT(appointment_id) AS appointment_count
FROM Doctors as d
JOIN Appointments as a ON d.doctor_id = a.doctor_id GROUP BY specialty;
GROUP BY specialty;

Explanation

• In this query, you’re joining the “Doctors” table (d) with the “Appointments” table (a) based on the doctor’s ID (doctor_id).
• As the outcome of interest is counting the appointments based on the doctor’s specialty, use the GROUP BY clause in the “specialty” column.
• The result will show the total appointments for each doctor’s specialty.

Output

2. The HAVING clause

The HAVING clause goes hand in hand with the GROUP BY clause but is used to filter further the data aggregated by the GROUP BY clause. On the broader aspect, it essentially refines your already filtered data based on specific criteria. This type of data segmentation assists in targeted analysis, improving understanding of specific subsets of data.

Note: The HAVING clause doesn’t allow the use of aliases

Query

SELECT specialty, COUNT(appointment_id) AS appointment_count
FROM Doctors as d
JOIN Appointments as a ON d.doctor_id = a.doctor_id
GROUP BY specialty
HAVING COUNT(appointment_id) > 1;

Explanation

This code filters out the aggregated data and shows results for specialties with more than 1 appointment obtained using the ‘ HAVING’ clause.

Output

3. The ORDER BY clause

The ORDER BY clause is used to query results based on one or more columns within the database. The output can be arranged in ascending or descending order of importance. Overall, the ORDER BY clause enhances the visual presentation of the data and aids in efficient analysis.

Note: One can use an alias in the ORDER BY clause.

Query

SELECT first_name, last_name
FROM Patients
ORDER BY last_name ASC;

Explanation

• The query obtains first_name and the last_name from the Patients table.
• The ORDER BY query alphabetically sorts the output using the “last_name” column (ASC).

Output

In conclusion, these clauses offer powerful tools for querying and analyzing data in SQL, providing a comprehensive approach to extracting valuable insights and presenting information in a structured and meaningful manner.