Learn SQL aggregate functions like COUNT, AVG, SUM, MIN, and MAX to summarize data across groups of records. The tutorial covers how NULL values are handled differently by each function, using DISTINCT to filter duplicates, and creating readable column names with AS aliases.
Chapters
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 Sum count, min and Max. SelectCountStar 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 yearofbird 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 add the keyword distinct to the query. For example, we select the distinct countries from the customer table showing us all countries that 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 descending 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.
