Course Content
Intermediate SQL
Intermediate SQL
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:
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 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:
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 thedepartment
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:
- The
WHERE
clause is used before data aggregation, while theHAVING
clause is used after data aggregation; - The
WHERE
clause is written beforeGROUP BY
, while theHAVING
clause is written afterGROUP 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
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.
Thanks for your feedback!
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:
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 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:
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 thedepartment
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:
- The
WHERE
clause is used before data aggregation, while theHAVING
clause is used after data aggregation; - The
WHERE
clause is written beforeGROUP BY
, while theHAVING
clause is written afterGROUP 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
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.
Thanks for your feedback!
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:
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 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:
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 thedepartment
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:
- The
WHERE
clause is used before data aggregation, while theHAVING
clause is used after data aggregation; - The
WHERE
clause is written beforeGROUP BY
, while theHAVING
clause is written afterGROUP 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
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.
Thanks for your feedback!
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:
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 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:
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 thedepartment
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:
- The
WHERE
clause is used before data aggregation, while theHAVING
clause is used after data aggregation; - The
WHERE
clause is written beforeGROUP BY
, while theHAVING
clause is written afterGROUP 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
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.