SQL Tutorial: Aggregations - summarizing data

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

---
For decision making, it is usually not important to look at individual records, but rather to examine summaries of certain groups. For example, we will look at the average rating or count the number of views of a movie, rather than looking at each rating and movie rental separately.

In this query, the column renting_price from the table movies is aggregated by using the AVG function - short for average. So we get the average price for renting a movie.

Here is a list of some more aggregate functions which we will use in this course: SUM, COUNT, MIN, and MAX.

SELECT COUNT star counts the number of rows of the selected table 'actors'. The result is 145.

Applying the function COUNT() to a column returns the number of not NULL values in this column. So we get the same result when we count the number of entries in the column 'name', since there are no NULL values in this column.

The column year_of_birth has two NULL values. That's why the third query returns 143 instead of 145.

Also with the aggregate functions AVG, SUM, MIN, or MAX the NULL values are always ignored.

Another very useful keyword is DISTINCT. If we want to filter out duplicates and only look at unique values then we can add the keyword DISTINCT to the query. For example, we select the distinct countries from the customer table, showing us all countries where customers come from.

Then we can count the distinct countries, which gives us the result 11.

From this query we can learn two things about NULL values. The column rating from the table renting has values 1 to 10 or null. 'DISTINCT rating' lists all of these values including NULL. The last line of the query orders the resulting table by the values of rating. It is ordered ascending and null is at the final position, so null is treated as the largest value when we order it.

In the SELECT clause, the term AS can be added to define names of a column in the return table. These new column names are also called aliases or shortcut names. Using alias names makes the resulting table more self-explanatory.
In this result table we have the average price and the number of genres.

Now it's time to put what you just learned into practice.

#SQLTutorial #DataCamp #DataDriven #DecisionMaking #SQL #Aggregations #data