Скачать или смотреть SQL Tutorial: Working with aggregate functions

Want to learn more? Take the full course at https://learn.datacamp.com/courses/ap... at your own pace. More than a video, you'll learn hands-on coding & quickly apply skills to your daily work.

---
Welcome to the final lesson of Chapter one. In this lesson you will review the use of aggregate functions.

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.

An excerpt of the data in the film table is provided here to illustrate how aggregate functions work.

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.

You can imagine the GROUP BY statement partitioning the data just like so.

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.

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.

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.

Now that you have an intuition for how aggregate functions work let's practice using them.

#SQLTutorial #DataCamp #SQL #RealWorld #Problems #aggregate #functions