Putting it together
SQL Aggregate Functions
SQL GROUP BY
SQL INNER JOIN
SQL Outer Joins
SQL LEFT JOIN
SQL RIGHT JOIN
SQL Joins Using WHERE or ON
SQL FULL OUTER JOIN
SQL Joins with Comparison Operators
SQL Joins on Multiple Keys
SQL Self Joins
SQL Analytics Training
Learn Python for business analysis using real-world data. No coding experience necessary.
The Collaborative Data Science Platform
Starting here? This lesson is part of a full-length tutorial in using SQL for Data Analysis. Check out the beginning.
In this lesson we'll cover:
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:
SELECT year, month, MAX(high) AS month_high FROM tutorial.aapl_historical_stock_price 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: