Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.HAVING | Grouping
Intermediate SQL
course content

Course Content

Intermediate SQL

Intermediate SQL

1. Grouping
GROUP BYOrder of StatementsTotal Ride Time for Each Subway Line ChallengeAverage Student's Grade ChallengeSearching for the Top Math Students ChallengeHAVINGSeveral Aggregate Functions in the QueryMore Complex Filtering
2. Nested Subqueries
Nested QueriesNon-tech Department Employees ChallengeUsing Inner Subqueries in the FROM SectionEmployees With More Than Average Salary ChallengeUNION ClauseCombining Table Results
3. Joining Tables
JOIN 2 TablesGet Category By Product PriceGet Minimum Price By CategoryGet Product Amount By CategoryLEFT, RIGHT and INNER JOINsTable Join PracticeFULL JOIN Usage
4. DDL and DML in SQL
CREATE and ConstraintsALTER and INSERT OperationsDROP and TRUNCATEUPDATE and DELETE

book
HAVING

The school is very grateful for your work, and now there is a new task for us.

It turns out that some students took additional exams when they were supposed to take only one. The school suspects them of cheating because each student should have only one grade.

We've been tasked with getting the last names of these students and passing them on to the school administration for them to take whatever action they deem necessary.

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
copy

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 understand what it is and how to use it, using an example from our employee table.

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.

Let's see how we can do this:


              
1234

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

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:

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

  1. The WHERE clause is used before data aggregation, while the HAVING clause is used after data aggregation;
  2. 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.

Here is the preview of a student_grades table we are working with:

Task
test

Swipe to show code editor

You need to fetch the last names of students who have multiple grades for the school.

You only need to retrieve the last names of the students; there's no need to include the count of their grades in the response. Use the HAVING clause and the COUNT() aggregate function to accomplish this task. Then, sort the surnames in the alphabetical order.

Note:

You should have only one column with last names in the result.

Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
Everything was clear?

How can we improve it?

Thanks for your feedback!

Section 1. Chapter 6
toggle bottom row

book
HAVING

The school is very grateful for your work, and now there is a new task for us.

It turns out that some students took additional exams when they were supposed to take only one. The school suspects them of cheating because each student should have only one grade.

We've been tasked with getting the last names of these students and passing them on to the school administration for them to take whatever action they deem necessary.

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
copy

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 understand what it is and how to use it, using an example from our employee table.

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.

Let's see how we can do this:


              
1234

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

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:

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

  1. The WHERE clause is used before data aggregation, while the HAVING clause is used after data aggregation;
  2. 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.

Here is the preview of a student_grades table we are working with:

Task
test

Swipe to show code editor

You need to fetch the last names of students who have multiple grades for the school.

You only need to retrieve the last names of the students; there's no need to include the count of their grades in the response. Use the HAVING clause and the COUNT() aggregate function to accomplish this task. Then, sort the surnames in the alphabetical order.

Note:

You should have only one column with last names in the result.

Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
Everything was clear?

How can we improve it?

Thanks for your feedback!

Section 1. Chapter 6
Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
We're sorry to hear that something went wrong. What happened?
some-alt