Title - GROUP BY SQL clause Tags - sql clause
GROUP BY is a clause used with aggregate functions.
It can only be placed:
After a WHERE statement, Before ORDER BY, or Before LIMIT.
If you wanted to know the average IMDB rating of movies in each year, you could write multiple queries like this:
SELECT AVG(imdb_rating) FROM movies WHERE year = 1999;
SELECT AVG(imdb_rating) FROM movies WHERE year = 2000;
SELECT AVG(imdb_rating) FROM movies WHERE year = 2001;
But GROUP BY makes it easier.
SELECT year, AVG(imdb_rating) FROM movies GROUP BY year ORDER BY year;
GROUP BY can also apply a calculation to a column. E.g., if you want to knwo how many movies have IMDb ratings that round to 1, 2, 3, 4, 5:
SELECT ROUND(imdb_rating), COUNT(name) FROM movies GROUP BY 1 ORDER BY 1;
1 refers to the first column in the SELECT statement, ROUND(imdb_rating).