Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
537 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andy Brown
In this tutorial
This part of our SQL training tutorial looks at the GROUP BY clause.
Aggregating data involves taking a set of rows and summarising it. For example, you might take a set of films (movies, to those in the US) ...
A list of films without any aggregation.
... and show the total number of Oscars (wins and nominations) by country:
The aggregated data, showing the total number of Oscar wins and nominations for each film-making country in our database.
One way to think of grouping by a column is that you start with a bucket for each country's data, and then add, count, average or perform some other statistic on all of the films in each country's bucket. Then again, this may not help you!
The above query would group the data by one column (the country). If you don't group it by any columns, you'll get a single row:
The total figure for all films.
Here are the possible functions that you can use:
Function | What it does |
---|---|
SUM | Returns the total value of a column or field. |
AVG | Returns the average value of a column or field. |
MIN | Returns the smallest number or date. |
MAX | Returns the largest number or date. |
COUNT | Returns the number of records. |
COUNT_BIG | Returns the number of records as a number of data type bigint. |
STDEV | Returns the standard deviation. |
STDEP | Returns the standard deviation for the population. |
VARP | Returns the variance for the population. |
The basic syntax of the GROUP BY clause is this (where XXX is any of the aggregation functions listed above, such as SUM, COUNT, etc):
SELECT
-- zero, one or more group by columns
FirstColumnGroupingBy,
SecondColumnGroupingBy,
...
-- one or more aggregated columns
XXX(FirstColumnAggregating),
XXX(SecondColumnAggregating),
...
FROM
TableName
GROUP BY
FirstColumnGroupingBy,
SecondColumnGroupingBy,
...
This doesn't mean much on its own, so let's look at a practical example: the query which shows the total number of Oscars by country at the top of this page. Here it is:
SELECT
c.CountryName,
SUM(f.FilmOscarWins) AS 'Wins',
SUM(f.FilmOscarNominations) AS 'Nominations'
FROM
tblFilm AS f
INNER JOIN tblCountry AS c
ON f.FilmCountryId=c.CountryId
GROUP BY
c.CountryName
In a grouping query, the rule is:
Every non-aggregated column in your query must be repeated in the GROUP BY clause.
What does this mean? In our example, we have 3 columns of output:
The three columns include two aggregated fields plus the column that we're grouping by (the country name).
The following query would not work, and would generate an error:
SELECT
c.CountryName,
SUM(f.FilmOscarWins) AS 'Wins',
SUM(f.FilmOscarNominations) AS 'Nominations'
FROM
tblFilm AS f
INNER JOIN tblCountry AS c
ON f.FilmCountryId=c.CountryId
This is because we're trying to show the country alongside a couple of summed fields, and we haven't said that we want to group by this column.
You can group by as many columns as you like. This query shows the number of films in our database for each director and studio (so we're grouping by two columns):
SELECT
d.DirectorName,
s.StudioName,
COUNT(f.FilmId) AS 'Number films'
FROM
tblFilm AS f
INNER JOIN tblDirector AS d
ON f.FilmDirectorId=d.DirectorId
INNER JOIN tblStudio AS s
ON f.FilmStudioId=s.StudioId
GROUP BY
d.DirectorName,
s.StudioName
ORDER BY
'Number films' DESC
Here are the first few rows produced by this query:
What does this show? That Richard Donner is the most loyal person, directing no less than 5 fllms for Warner Brothers?
You can group by any calculated expression, as this example shows:
SELECT
YEAR(FilmReleaseDate) AS 'Release year',
COUNT(FilmId) AS 'Number films'
FROM
tblFilm
GROUP BY
YEAR(FilmReleaseDate)
This would show the number of films released in a given year:
The query shows how many films there are for each year.
If you don't group by any columnns, you get a single summary statistics row (as mentioned above):
SELECT
SUM(FilmOscarWins) AS 'Total wins',
SUM(FilmOscarNominations) AS 'Total nominations'
FROM
tblFilm AS f
This query doesn't have any grouping, so produces one row:
if you don't group, you only get one row of data in the results set!
Now that I've covered the basics of grouping, let's look at how you can apply criteria to a grouped query using WHERE and HAVING.
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2025. All Rights Reserved.