HAVING | BUS 104 – SQL Exam

Still feeling nervous for your exam? Check out the SQL Cram Kit for practice questions that emulate the difficulty that you'll face on your exam. Each question includes a thorough step-by-step explanation where I walk you through the entire problem solving process!

HAVING explained

Meet Mary, an avid runner and fitness guru.

Mary is competing to be selected to participate in The Best Race Ever against 2 other runners, Sam and David.

Each runner must run a mile 3 times and record their times.

Mary ran the following times: 8:05, 7:45, 8:07.

Sam ran the following times: 8:17, 8:01, 7:54.

David ran the following times: 7:48, 8:00, 8:06.

In order to make the cut to run in The Best Race Ever, the runner's average mile time must be below 8 minutes.

So, let's find each runners' average time. First, we'll convert their times to seconds.

Mary: 485, 465, 487
Sam: 497, 481, 474
David: 468, 480, 492

Next, we'll find the average seconds…

Mary: (485 + 465 + 487) / 3 = 479 avg. seconds
Sam: (497 + 481 + 474) / 3 = 484 avg. seconds
David: (468 + 480 + 486) / 3 = 478 avg. seconds

…and divide by 60 seconds to find the average minutes.

Mary: 479 / 60 = 7.98 avg. minutes
Sam: 484 / 60 = 8.07 avg. minutes
David: 478 / 60 = 7.97 avg. minutes

Now, let's cross out any times that aren't below 8 minutes.

Mary: 479 / 60 = 7.98 avg. minutes
Sam: 484 / 60 = 8.07 avg. minutes
David: 478 / 60 = 7.97 avg. minutes

Looks like Mary and David made the cut for The Best Race Ever!

In this situation, we essentially performed the purpose of a HAVING statement. We took groups of running times for each runner, averaged them, and then filtered out any results that weren't below 8 minutes.

HAVING statements are used to filter the results of a query, based on aggregate function outputs.

How to code a HAVING

HAVING condition(s);

Let's break down how we can write these conditions.

Every condition is typically structured like so:

HAVING aggregate-function comparison-operator value;

Let's apply this to the race situation above.

Here is our SQL table of times in seconds.

Notice how these records match exactly what we stated before:

Mary: 485, 465, 487
Sam: 497, 481, 474
David: 468, 480, 492

Now, we must find the average time for each runner. Let's set up a SELECT and FROM statement for this.

SELECT column1, column2
FROM database_name.table_name;

So, what's going to go in column1? Well, we'd like to see each runners name, and then their average time next to it. So, we'll place the name field in column1...

SELECT name, column2
FROM database_name.table_name;

...and for column2, we'll use the aggregate function AVG, with time_in_sec field inside to see the average time.

SELECT name, AVG(time_in_sec)
FROM database_name.table_name;

Before you move on… WAIT.

There is something crucial that we must do here. If you read my GROUP BY | SQL in 30 Minutes article, you may have an idea.

Whenever you have field(s) and aggregate function(s) in your SELECT statement, you must include all field(s) in your GROUP BY statement.

Do we have field(s) in our SELECT statement? Yes, name.

SELECT name, AVG(time_in_sec)
FROM database_name.table_name;

Do we have aggregate function(s)? Yes, "AVG(time_in_sec)".

SELECT name, AVG(time_in_sec)
FROM database_name.table_name;

So, we must place the field(s), name, in a GROUP BY statement.

SELECT name, AVG(time_in_sec)
FROM database_name.table_name
GROUP BY name;

Now, let's get this FROM statement done. For the sake of this situation, let's say the database is called the_best_race_ever and the table is called runner_times. (Don't pay too much attention to this, we just need to get our FROM statement done.)

SELECT name, AVG(time_in_sec)
FROM the_best_race_ever.runner_times
GROUP BY name;

When we run this query, we get the following results:

We're on the right path, because this matches what we stated earlier.

Mary: (485 + 465 + 487) / 3 = 479 avg. seconds
Sam: (497 + 481 + 474) / 3 = 484 avg .seconds
David: (468 + 480 + 486) / 3 = 478 avg. seconds

Remember, we only want average times less than 8 minutes. So, first we must convert these average seconds into average minutes by dividing "AVG(time_in_sec)" by 60.

SELECT name, AVG(time_in_sec)/60
FROM the_best_race_ever.runner_times
GROUP BY name;

Once again, we're in line with what we said earlier.

Mary: 479 / 60 = 7.98 avg. minutes
Sam: 484 / 60 = 8.07 avg. minutes
David: 478 / 60 = 7.97 avg. minutes

Now, for the HAVING statement.

SELECT name, AVG(time_in_sec)/60
FROM the_best_race_ever.runner_times
GROUP BY name
HAVING aggregate-function comparison-operator value;

We only want "AVG(time_in_sec)/60" values less than 8 minutes. So, we'll place "AVG(time_in_sec)/60" as our aggregate function.

SELECT name, AVG(time_in_sec)/60
FROM the_best_race_ever.runner_times
GROUP BY name
HAVING AVG(time_in_sec)/60 comparison-operator value;

Now for our comparison operator. Let's step back here and understand what comparison operators are and how to use them. If you've already seen this table in WHERE | SQL in 30 Minutes, then click here to skip this.

Comparison Operators

comparison operator is used to determine if an aggregate function meets a condition. It compares values and returns either true or false.

Here's the list of the most common comparison operators, a description of them, and an example.

Now that we know what comparison operators are, which one should we use to state that the average time in minutes must be less than 8 minutes?

The "<" comparison operator…

SELECT name, AVG(time_in_sec)/60
FROM the_best_race_ever.runner_times
GROUP BY name
HAVING AVG(time_in_sec)/60 < value;

…with a value of 8.

SELECT name, AVG(time_in_sec)/60
FROM the_best_race_ever.runner_times
GROUP BY name
HAVING AVG(time_in_sec)/60 < 8;

And there you have it! Now, when we run this query, the following results get filtered out…

…and we're left with our end result below!

Notice how this matches exactly what we said before.

Mary: 479 / 60 = 7.98 avg. minutes
Sam: 484 / 60 = 8.07 avg. minutes
David: 478 / 60 = 7.97 avg. minutes

Emphasizing the difference between HAVING and WHERE

This is a common error to make as a beginner in SQL, so I'll make the difference plain and simple for you.

HAVING statements are used with aggregate functions.

WHERE statements are used with fields.

The reason we used a HAVING statement above is because we were filtering based on the value of an aggregate function.

SELECT name, AVG(time_in_sec)/60
FROM the_best_race_ever.runner_times
GROUP BY name
HAVING AVG(time_in_sec)/60 < 8;

This is an aggregate function because we're using the AVG aggregate function in it.

Example of filtering with WHERE

If we wanted to filter based on name, a field, we could do so with a WHERE statement.

For example, let's say we just wanted to see Mary's average time. We'd filter for records in which the value of the name field equals "Mary".

SELECT name, AVG(time_in_sec)/60
FROM the_best_race_ever.runner_times
WHERE name = 'Mary'
GROUP BY name;

And when we execute this query, the red rows would be filtered out…

…and we'd be left with the following result.

Notice how we didn't use a HAVING statement here, since name is not an aggregate function. It's a field. That's why we used a WHERE.

SQL Follow-along Guide

It's no secret you retain info better when you write it down. That's why I created the SQL Follow-along Guide for you!

As you come upon key concepts highlighted in yellow, like this sentence here, you can fill-in-the-blanks on your Follow-along Guide so that you remember all the important stuff for later.

You can obtain the SQL Follow-along Guide for FREE by entering your email below!

Now that you've got a good understanding of the difference between HAVING and WHERE, let's move onto learning how to sort results with ORDER BY!

Concepts

Each exam concept broken down with relatable situations to your life!

Practice Problems

Include step-by-step explanations through each part of the problem!

"The SQL Cram Kit was the best thing I could’ve done before my exam. It takes practice and more practice to be good at coding and that is what I needed."

Andrew Green
October 29, 2020

Rating: 5 out of 5.