GROUP BY Clause

This YouTube video was created by Steve Griffith.

The GROUP BY clause can be used with Aggregate Functions to group the result by one or more columns. The effect of using the GROUP BY clause on the results of an Aggregate Function will include a separate row for each group with the aggregate calculation occurring only for the data related to that group.

-- Get the total number of movies for each year
SELECT COUNT(`movie_id`) as `total`, `year`
FROM `movies`
GROUP BY `year`;

Try it yourself on DB Fiddle

It is possible to group rows across multiple tables using the INNER JOIN clause with the GROUP BY clause.

-- Get the total number of movies for each genre title
SELECT COUNT(movie_id) as total, g.genre_title
FROM movies AS m INNER JOIN genres AS g
ON m.genre_id = g.genre_id
GROUP BY g.genre_title;

Try it yourself on DB Fiddle

When using aggregate functions, for every column that you wish to display beyond the aggregate column, it must also be added as a GROUP BY column. It is also possible to filter the results by using the WHERE clause.

-- Get the total number of movies before the year 2000 
-- for each genre title and directors ordered by the total number of movies
SELECT COUNT(movie_id) as total, g.genre_title, m.director
FROM movies AS m INNER JOIN genres AS g
ON m.genre_id = g.genre_id
WHERE m.year < 2000
GROUP BY g.genre_title, m.director
ORDER BY total DESC;

Try it yourself on DB Fiddle

HAVING Clause

The WHERE clause is used to filter results BEFORE any grouping occurs. As such, the WHERE clause cannot be used with aggregate functions. Instead, the HAVING clause should be used. The HAVING clause also filter results, but does AFTER grouping occurs and can be used with aggregate functions.

-- Total number of movies by year
-- for years having more than 1 movie
SELECT COUNT(*) as total, year
FROM movies 
GROUP BY year
HAVING total > 1