Online College Courses for Credit

+
1 Tutorials that teach HAVING to Filter On Aggregates
Take your pick:
HAVING to Filter On Aggregates

HAVING to Filter On Aggregates

Rating:
(0)
Author: Sophia Tutorial
Description:

Modify a given query that uses GROUP BY to eliminate a portion of the groups displayed in the results.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
This tutorial explores using the HAVING clause to have a filter condition on groups and aggregates.

The HAVING clause allows us to specify a search condition for a group or an aggregate. It is used with the GROUP BY clause that divides rows into groups. You are already familiar with the WHERE clause. The WHERE clause filters individual rows based on a specified condition. The HAVING clause filters the groups of rows based on a set of conditions. They are similar but separate from one another.

Let us take a look at the invoice table to find the SUM of the total that’s grouped by country:

SELECT billing_country, SUM(total)
FROM invoice
GROUP BY billing_country;

File:11321-1370-1.png

We could not use the WHERE clause in this case as the WHERE clause only looks at individual rows and not the group of rows. If we wanted to only list the countries that had the sum of the total to be greater than 50, we would add the HAVING clause to compare the aggregate function after the GROUP BY clause. This would not work in the WHERE clause like:

SELECT billing_country, SUM(total)
FROM invoice
WHERE total > 50
GROUP BY billing_country;<be></be>

This would look at individuals rows that had a total > 50 instead of the groups. Adding the comparison specifically to the SUM of the total would look like this:

SELECT billing_country, SUM(total)
FROM invoice
GROUP BY billing_country
HAVING SUM(total) > 50;

Note too that we don’t have to have the same aggregate functions listed in the SELECT clause and HAVING clause. For example, if we wanted to show the number of invoices having the total amount by country > 50, we can change the SUM to COUNT in the SELECT clause:

SELECT billing_country, COUNT(total)
FROM invoice
GROUP BY billing_country
HAVING SUM(total) > 50;

We could also sort the results using the aggregate function as well:

SELECT billing_country, COUNT(total)
FROM invoice
GROUP BY billing_country
HAVING SUM(total) > 50
ORDER BY SUM(total);

We can use a variety of aggregate functions like the COUNT to help filter. For example, we may want to find the countries and the number of customers that have the number of counters being greater than 5:

SELECT country, COUNT(*)
FROM customer
GROUP BY country
HAVING COUNT(*) > 5;

We could have multiple conditions to filter based on aggregate values by using the AND and OR operators in the HAVING clause. We may want to filter the group further to have ensured that the number of customers also checks for the number of customers that have a fax count greater than two. Note that as a reminder counting a specific column only counts the non-null values so if there is no fax, the row isn’t counted unlike using COUNT(*) where it counts the number of rows.

SELECT country, COUNT(*),COUNT(fax)
FROM customer
GROUP BY country
HAVING COUNT(*) > 5 AND COUNT(fax) > 2;

try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.

summary
The HAVING clause allows us to add a search condition for a group or aggregate from a GROUP BY clause.