Learn HAVING Clause

The school information system is being reviewed to ensure data consistency and accuracy. As part of this review, a new data analysis task has been assigned.

During routine checks, it was discovered that some student records contain more than one grade entry, even though the system is designed to store only a single grade per student.

Your task is to help identify such cases for further internal review.

Let's think together about how we can do this. You could start by considering that we can do this using a WHERE clause, and it would look something like this:


              123
            
SELECT student_surname
FROM student_grades
WHERE COUNT(grade) > 1

But, as you can see, we get an error indicating that we cannot use aggregate functions inside a WHERE clause. This is where we'll need the HAVING clause.

Let's say we need to retrieve the departments where employees' average salary is below $70,000 per year. To achieve this, we'll need to use an aggregate function and the HAVING clause:


              1234
            
SELECT department
FROM employees
GROUP BY department
HAVING AVG(salary) < 70000

We received one department in response using the HAVING clause, where we set a condition for the column by which we grouped the data.

Note

To use data aggregation within the HAVING clause, we need to have data grouping in our query. As in the query above, we grouped the data by the department column.

Let's look at the more generalized syntax of the HAVING clause and when it's best to use it:

SELECT column1, column2 --(optional)
FROM table
GROUP BY column1
HAVING AGG(column_n) --(condition)

Let's also briefly understand the main difference between WHERE and HAVING clauses and when to use each of them:

The WHERE clause is used before data aggregation, while the HAVING clause is used after data aggregation;
The WHERE clause is written before GROUP BY, while the HAVING clause is written after GROUP BY.

These are the two main differences you need to remember for successful use of the HAVING clause. Now, let's return to the task given to us by the school.

Task

Swipe to start coding

Some students appear multiple times in the grade records, which indicates duplicate or unexpected entries in the system.

You need to retrieve the last names of all students whose records contain more than one grade entry. The result will be used by the school administration to review and correct the data if necessary.

Return only the list of last names that meet this condition, sorted alphabetically.

Brief Instructions

Retrieve the student_surname column.
Group the data by student_surname.
Use the HAVING clause to filter the results based on COUNT(grade) > 1.
Sort the results by student_surname.

Solution

Everything was clear?

Thanks for your feedback!

Section 1. Chapter 6

single

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Suggested prompts:

Can you show me how to use the HAVING clause to find students with multiple grade entries?

Can you explain why the HAVING clause works in this scenario but WHERE does not?

Can you provide an example query for the school task using HAVING?

Swipe to show menu