Explains how to filter a result set on its grouped data, rather than filtering the rows to be grouped (as with the WHERE clause)
- [Instructor] Now if we want to apply a filter to the new data we've created, this count function here, we would use HAVING. HAVING is like a WHERE clause for aggregated data, or grouped data. It's a good tip to make sense of SQL queries in sections. So first of all, we've got SELECT, FROM and WHERE, if there were to be a WHERE clause. Then we have GROUP BY, and HAVING, if there is a HAVING clause.
And then finally, we have our ORDER BY at the end. And SQL queries always follow this pattern. So let's use one. Now we're going to say HAVING count (district) greater than 8. And you can see it's returned us where the count are 9 and 10, and instead of repeating the aggregation, we can just use the alias there that we've got, ct.
So we could say HAVING ct greater than 8. And that returned us exactly the same thing. Generally speaking, when building a query, you would select the thing that you're also going to group by, but you don't have to. So you could run. So you could just return the districts without the count. And that works fine as well. Typically, you would select the aggregation you later intend to filter by, simply because things can get confusing very quickly.
And that's because there are lots of other aggregation functions. And let's have a look at some of those now. So if we go to the film table, I've picked this table because it has a number of numeric columns with different data. So you can see rental_duration, for example, contains a reasonable diversity of integers. So we can say, put that on a new line, and say SELECT min (rental_duration). And there we go, the smallest one is 3.
And the largest item is 7. So we know that we have a thousand values ranging from 3 and 7 in this field. Now let's try sum and average. The sum of that entire field is just shy of 5,000. And the average is just short of 5, which we'd expect given that the minimum is 3 and the maximum is 7. Now let's make that query a little more interesting.
Instead of returning one value for the whole table, we're going to say GROUP BY rating. And I'm going to pop that field up into the SELECT statement as well. So we can see different ratings are given here for the films, and the average rental duration differs slightly for each of them, generally around 5 for all of them. So many of these numeric functions aggregate our data. With string and date functions, we input a row, performed the function on it, and output a row.
If we input 603 rows, we output 603 rows. And the functions were sort of working on one data point at a time, but with numeric functions, you tend to work on a whole set of data points at a time. It's not usually meaningful to extract something from a number as we might extract a partial string, or part of a date. Generally, we want to compare or analyze an entire data set. Now, numeric functions can of course be used in combination with other kinds of functions, just as we've seen by combining string functions earlier.
So we could say SELECT avg(year(last_update)) FROM actor. Then we can see it returns us just above 2006.
Join Emma Saunders as she shows you how to design and write simple SQL queries for data reporting and analysis. Review the different types of SQL, and then learn how to filter, group, and sort data, using built-in SQL functions to format or calculate results. Learn a bit about data types and database design. Discover how to perform more complex queries, such as joining data together from different database tables. Last but not least, Emma shows how to save your queries as views, so you can run them again and again.
- Using different versions of SQL
- Retrieving data with SELECT statements
- Filtering and sorting your results
- Transforming results with built-in SQL functions
- Grouping SQL results
- Merging data from multiple tables
- Identifying data types, and how to make sense of your database design
- Saving SQL queries