Working with aggregate functions
1. Working with aggregate functions
Welcome to the final lesson of Chapter one. In this lesson you will review the use of aggregate functions.2. GROUP BY
Let's unpack how to use and understand aggregate functions by reviewing the following query. This query uses the aggregate function, AVG, to generate the average replacement cost for each film rating.3. GROUP BY
An excerpt of the data in the film table is provided here to illustrate how aggregate functions work.4. GROUP BY
Every aggregate function requires a GROUP BY statement to specify which column or columns are used for aggregation. In this query we aggregate by the rating of the film.5. GROUP BY
You can imagine the GROUP BY statement partitioning the data just like so.6. GROUP BY
And the aggregate function, in this case the average function creating a summarized value for each data partition. Take a moment to review this visualization of the aggregate function in action. The way I like to think of it is that the GROUP BY function funnels all of the rows within each rating into the aggregate function, in this case the average. The result is an average of the column of choice for the respective rows. I hope that the image shown here will help you build an intuition for how aggregate functions work.7. Numeric aggregate functions
In addition to the average function, which returns the mean of a numeric element, there are other numeric aggregation functions such as COUNT which counts the elements in the partitions or SUM which can be used to sum a numeric column. In this query, the three aggregate functions are used to generate the average cost, the number of elements and the total replacement costs for each film rating.8. String aggregate functions
It is also possible to aggregate strings as well. The STRING_AGG function is used to concatenate strings for all elements in a GROUP BY partition. The function requires two arguments, the column to concatenate and the separator string used to separate the individual elements. In this example, the STRING_AGG function is used to combine the film titles for each rating using the comma as a separator. The result of this query is a list of comma-separated film titles for each rating category.9. Time to aggregate!
Now that you have an intuition for how aggregate functions work let's practice using them.Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.