Get startedGet started for free

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.