HAVING vs. WHERE in SQL

Unable to SELECT with an aggregate function in SQL? You’ve come to the right place!

Photo by Claudio Schwarz on Unsplash

What You’ll Learn

• The difference between WHERE and HAVING in SQL
• How to use WHERE in SQL
• How to use HAVING in SQL
• How to use WHERE + HAVING in SQL

Prerequisite Knowledge

Before hopping into the difference between HAVING and WHERE, you should be familiar with

• Terminal commands
• SQL Tables
• The SELECT clause
• Aggregate functions in SQL (SUM, MAX, MIN, etc.)

Level 0: Getting Started

In this post, we will investigate WHERE and HAVING in several contexts. While not essential, it might be useful to recreate the tables and data used as examples. To get started, navigate to an open directory in your terminal and paste the following code:

sqlite3 where_having.db

PRESS ENTER

CREATE TABLE students(
name TEXT,
gpa REAL); 

PRES ENTER

CREATE TABLE student_credits(
name TEXT, 
credits REAL, 
semester REAL);

PRESS ENTER

INSERT INTO students (name, gpa) VALUES ("Steve", 3.9);
INSERT INTO students (name, gpa) VALUES ("Jane", 4.0); 
INSERT INTO students (name, gpa) VALUES ("Jon", 3.1);

PRESS ENTER

INSERT INTO student_credits (name, credits, semester) 
VALUES ("Steve", 10, 1);
INSERT INTO student_credits (name, credits, semester) 
VALUES ("Steve", 11, 2);
INSERT INTO student_credits (name, credits, semester) 
VALUES ("Steve", 15, 1);
INSERT INTO student_credits (name, credits, semester) 
VALUES ("Jon", 5, 1);

PRESS ENTER

SELECT * FROM students;

If you pasted it correctly, you should see a table with some information about students and their GPAs. If not, try again or brush up on your use of the terminal.

An Excel representation of our data

Level 1: SELECT with Simple Condition

Say you want to SELECT students with a GPA above the cutoff required for a scholarship. A quick Google search will reveal the WHERE clause that tells SQL to only select entries in a database where some other condition is met. Let’s look at an example that assumes some table full of students:

--returns all student names
SELECT name FROM students;
--returns all student names with gpa above 3.5
SELECT name FROM students WHERE gpa > 3.5;

Great, we only see Jane and Steve! These commands are easy and intuitive to read. Why don’t we make things more complicated?

Level 2: Aggregate Methods && HAVING

Let’s change the scenario. Now, we have a table called student_credits that contains information on how many credits each student took in a given semester.

The student_credits table in Excel

In this situation, let’s assume that our scholarship only requires that a student has taken more than 30 total credits. So, we want to SELECT a student’s name WHERE the sum of all of their credit hours is greater than 20. Let’s try the approach above and directly translate this command into SQL.

NOTE: The following code will not work

SELECT name FROM student_credits WHERE SUM(credits) > 30;

Oh no! This code outputs an error about misusing the aggregate method SUM. Looks like we are going to have to try something else: the HAVING command.

Get Mark Viti’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Let’s try the following code:

SELECT name 
FROM student_credits 
GROUP BY(name) HAVING SUM(credits)>30;

Wow! Only Steve was returned, exactly as we had hoped! But what’s going on here? What’s HAVING doing that WERE isn't? Let’s take a closer look…

Level 3: Understanding HAVING && WHERE

First, we can see that HAVING allows aggregate methods, like SUM, to be used as SELECT criteria. Why can’t WHERE use aggregate methods?

In short, WHERE can only see one row at a time, so it has to filter line by line. An aggregate method like SUM is looking at many rows at once, and so WHERE does not know what to do with SUM and other aggregate methods.

On the other hand, HAVING was designed to work in conjunction with the GROUP BY clause. So, in order to understand HAVING, we have to understand GROUP BY first.

Level 3.1: Understanding GROUP BY

In SQL, GROUP BY is a powerful clause that allows us to ‘group’ rows together based on a particular value. Let’s go back to our student_credits example:

SELECT name 
FROM student_credits
GROUP BY(name); 

which outputs

Jon
Steve

But remember, there were three entries for Steve, so GROUP BY has consolidated those three rows down to one row. Cool, huh?

Level 3.2: GROUP BY + HAVING

When we then add our HAVING clause to GROUP BY, we are essentially adding a WHERE statement that looks at the rows based on the value we grouped by. This allows HAVING to look at many rows at once, allowing it to working with aggregate methods! Remember, because HAVING requires GROUP BY, we always need to use a GROUP BY clause before we can add HAVING. Take a look back at our code, can you explain exactly what’s going on?

SELECT name 
FROM student_credits 
GROUP BY(name) HAVING SUM(credits)>30;

Level 4: Using WHERE + HAVING Together

Let’s try one more table. Copy the following code into your terminal:

CREATE TABLE scholarship(
name TEXT, 
credits REAL, 
semester REAL,
gpa REAL);

PRESS ENTER

INSERT INTO scholarship (name, credits, semester, gpa) 
VALUES ("Steve", 10, 1, 3.9); 
INSERT INTO scholarship (name, credits, semester, gpa) 
VALUES ("Steve", 11, 2, 3.9);
INSERT INTO scholarship (name, credits, semester, gpa) 
VALUES ("Steve", 15, 3, 3.9);
INSERT INTO scholarship (name, credits, semester, gpa) 
VALUES ("Jane", 5, 1, 4.0);
INSERT INTO scholarship (name, credits, semester, gpa) 
VALUES ("Jane", 5, 2, 4.0);
INSERT INTO scholarship (name, credits, semester, gpa) 
VALUES ("Jane", 5, 3, 4.0);
INSERT INTO scholarship (name, credits, semester, gpa) 
VALUES ("Jon", 16, 1, 3.1);
INSERT INTO scholarship (name, credits, semester, gpa) 
VALUES ("Jon", 15, 2, 3.1);

PRESS ENTER

An Excel representation of our data

Now, let’s find students that have above a 3.5 GPA and have taken a total of 30 classes. From the data, it looks like Steve should be the only name our search returns. Since the GPA of every student is stored in every row, we can use WHERE to filter for students above a 3.5. However, since credits are different every semester, we need to use the aggregate method SUM to find total credits; hence, we must use a GROUP BY and HAVING clauses to get the desired result. Since GROUP BY has to precede HAVING, our final search is

SELECT name FROM scholarship 
WHERE gpa > 3.5
GROUP BY name 
HAVING SUM(credits) > 30; 

And boom! We only return Steve. If you followed allow in this post, this combination should have been intuitive. SQL did the following:

Step 1: WHERE removes GPAs below 3.5

Step 2: GROUP BY consolidates row information

Note: This is not exactly what is happening under the hood, but it is a helpful way to visualize GROUP BY

Step 3: Apply HAVING SUM to Credits

And our return value is Steve!

Feel free to mess around with the code provided to flesh out your understanding. Happy coding!

Check out these resources for more on SQL, GROUP BY, HAVING and WHERE.

• https://www.essentialsql.com/what-is-the-difference-between-where-and-having-clauses/
• https://www.geeksforgeeks.org/having-vs-where-clause-in-sql/
• https://learnsql.com/blog/group-by-in-sql-explained/

Go from SELECT * to interview-worthy project. Get our free 5-page guide.