Aggregate Functions

This YouTube video was created by Steve Griffith.

Aggregate functions are use to perform calculation on a multiples rows of a single column. Some common aggregate functions include: COUNT(), SUM(), and AVG().

NOTE

When using aggregate functions without a GROUP BY clause, only one column can be requested. If multiple columns are requested an error will occur.

COUNT() Function

The COUNT() function is used to count the number of non-NULL rows for a specific column. If the * is used in place of the column it will count every row returned.

-- Count all the rows with a movie id
SELECT COUNT(`movie_id`) FROM `movies`;

-- Count all the rows in the table
SELECT COUNT(*) FROM `movies`;

Try it yourself on DB Fiddle

SUM() Function

The SUM() function returns the sum of all the matched rows of the provided column.

NOTE

The SUM() function only works with columns with a number datatype.

-- Getting the sum total of the year column for every movie 
SELECT SUM(`year`) FROM `movies`;

Try it yourself on DB Fiddle

AVG() Function

The AVG() function will return the mean average of the values of the given column.

NOTE

The AVG() function only works with columns with a number datatype.

-- Getting the average year for all movies 
SELECT AVG(`year`) FROM `movies`;

Try it yourself on DB Fiddle

MIN() and MAX() Functions

The MIN() and MAX() functions are used to get the minimum or maximum values of the provided column.

NOTE

These functions can be used with string values.

-- The oldest movie in the movies table
SELECT MIN(`year`) as `Oldest` FROM `movies`;

-- The newest movie in the movies table
SELECT MAX(`year`) as `Newest` FROM `movies`;

Try it yourself on DB Fiddle

WHERE Clause

The WHERE clause can be used with aggregate functions to filter the results.

-- The newest movie with the genre 'Sci-Fi'
SELECT MAX(`year`) as `Newest`
FROM `movies`
WHERE `genre_id` = 
  (SELECT `genre_id` FROM `genres` WHERE `genre_title` = 'Sci-Fi' LIMIT 1);

Try it yourself on DB Fiddle