Starting here? This lesson is part of a full-length tutorial in using SQL for Data Analysis. Check out the beginning.
The SQL HAVING clause
In the previous lesson, you learned how to use the
GROUP BY clause to aggregate stats from the Apple stock prices dataset by month and year.
However, you’ll often encounter datasets where
GROUP BY isn’t enough to get what you’re looking for. Let’s say that it’s not enough just to know aggregated stats by month. After all, there are a lot of months in this dataset. Instead, you might want to find every month during which AAPL stock worked its way over $400/share. The
WHERE clause won’t work for this because it doesn’t allow you to filter on aggregate columns—that’s where the
HAVING clause comes in:
MAX(high) AS month_high
GROUP BY year, month
HAVING MAX(high) > 400
ORDER BY year, month
HAVING is the “clean” way to filter a query that has been aggregated, but this is also commonly done using a subquery, which you will learn about in a later lesson.
Query clause order
As mentioned in prior lessons, the order in which you write the clauses is important. Here’s the order for everything you’ve learned so far: