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`;
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`;
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`;
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`;
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);