GROUP BY and Aggregate Functions: A Complete Overview

SQL’s GROUP BY and aggregate functions are essential in data aggregation – and for analyzing data and creating reports. Let’s explore them together!  

In SQL, GROUP BY and aggregate functions are one of the language’s most popular features. Data aggregation is critical for data analysis and reporting; to make sense of all the data in a dataset, it often needs to be aggregated.

But what is data aggregation? Put simply, it’s when you group data based on common values and perform a calculation for each data group. For example, you might group a list of dogs by their breed and then use a calculation to figure out the average height for each breed. This task is made possible by the GROUP BY clause and the aggregate function AVG().

If you need to refresh your knowledge about GROUP BY, our SQL Basics course is ideal. It has 129 interactive exercises that you can finish in about 10 hours. It covers topics such as filtering data, using logical operators, joining, and using set operators.

The Basic Syntax of GROUP BY with Aggregates

GROUP BY is a clause in SQL that arranges data with the same values into groups. Grouping is done by column(s), where all the rows with the same value in that column belong to one group. You can then perform summary computations – such as counting, summing, or averaging values – for each group. For example, you can use GROUP BY to compute the number of employees in a department, the total salary or average salary per department, etc.

The syntax of GROUP BY with an aggregate function is:

SELECT column1, 
        column2, 
        ..., 
        aggregate_function(column_name)
FROM table_name
WHERE filtering_condition
GROUP BY column1, column2, …
HAVING filtering_condition;

The aggregate function is written in the SELECT statement, and the function’s result will be shown as an additional column.

The GROUP BY clause contains the columns by which you want your output to be grouped. This clause is often used with the WHERE and HAVING clauses for filtering. In the syntax, WHERE comes before GROUP BY, while HAVING comes after it. (I’ll explain more about these two clauses later.) Also, your data will sometimes contain NULLs, so you’ll be interested to learn how GROUP BY treats NULLs.

An example of GROUP BY in action is shown in the following GIF.

This basic example groups data by the film director and shows the number of films each has made.

SQL Aggregate Functions

SQL’s aggregate functions are used when you want to do computations on data and return a single value.

The most common aggregate functions in SQL are:

• COUNT() – Counts the rows in the stated column.
• SUM() – Returns the sum of all values.
• AVG() – Computes the average of a group of values.
• MIN() – Returns the minimum (the lowest) of the given values.
• MAX() – Returns the maximum (the highest) of the given values

It’s important to note that the COUNT() function has  several different use variations:

• COUNT(*) counts all rows in a result set, including NULL values and duplicates.
• COUNT(expression) counts non-NULL values in a result set.
• COUNT(DISTINCT) counts distinct values and ignores duplicates.

There are more details in our guide What is the Difference Between COUNT(*), COUNT(1), COUNT(column), and COUNT(DISTINCT)?

Using GROUP BY with SQL Aggregate Functions

I’ll use the table albums to show how aggregate functions and GROUP BY work together. The script for creating the table is here.

This table contains data about musical albums. Here’s a snapshot:

Example 1: COUNT() with GROUP BY

Here’s an example code that counts the number of albums by label:

SELECT label,
	 COUNT(*) AS number_of_albums
FROM albums
GROUP BY label; 

I select the label and use COUNT(*) to find the number of albums. The asterisk (*) is a shorthand for counting all rows. When I group the output by label, COUNT() will show the number of albums per label.

Here’s the output:

Example 2: SUM() with GROUP BY

The following code sums the album lengths to show the total music available per artist:

SELECT artist,
	 SUM(length) AS total_music_available
FROM albums
GROUP BY artist;

The SUM() function is applied to the length column, and the output is then grouped by the artist.

This is the code result:

Example 3: AVG() with GROUP BY

This code calculates each artist’s average album length:

SELECT artist,
	 AVG(length) AS average_album_length
FROM albums
GROUP BY artist; 

To get the desired result, you need to use AVG() on the column length and group the output by artist.

Here’s what the output looks like:

Example 4: MIN() and MAX() with GROUP BY

Let’s find the shortest and the longest album length for each year:

SELECT release_year,
	 MIN(length) AS minimum_album_length,
	 MAX(length) AS maximum_album_length
FROM albums
GROUP BY release_year
ORDER BY release_year; 

As in earlier examples, the aggregate functions are used on the length column; MIN() for the shortest and MAX() for the longest album.

The output is grouped by release year.

I also added the ORDER BY clause to sort the output from the earliest to the latest year so it would be more readable.

Here’s the code output:

Advanced GROUP BY Techniques

The previous section dealt with the basics of using SQL aggregate functions and GROUP BY. With these foundations set, we can now explore some advanced GROUP BY techniques.

Filtering Aggregated Results with HAVING

To filter aggregated results, you need to use the HAVING clause. Its syntax is shown here:

SELECT column1, 
       column2, 
       ..., 
       aggregate_function(column_name)
FROM table_name
GROUP BY column1, column2, …
HAVING filtering_condition;

HAVING compares the aggregated values to the filtering condition and returns only the values that satisfy that condition. You can learn more in our article about the HAVING clause.

Let me show you an example. The following code calculates the average album length per artist, but it shows only those whose average album length is under 40 minutes.

SELECT artist,
	 AVG(length) AS average_album_length
FROM albums
GROUP BY artist
HAVING AVG(length) < '00:40:00';

I used the AVG() function just the way I did earlier. The HAVING clause is new. To filter data in the required way, I wrote in HAVING the same average calculation as the one appearing in SELECT. Then, I use the comparison operator ‘less than’ (<) to compare each grouped result with a value (in this case, 40 minutes). This value is enclosed in single quotes (''). The value in the quotes has to be written in the format HH:MM:SS (hours:minutes:seconds) because the values in the column length are of the INTERVAL data type.

Here’s the output:

Grouping by Multiple Columns

So far, I have grouped data by one column. However, it’s also possible to group by two or more columns.

It’s not complicated: You just have to list all the grouping columns in GROUP BY and separate them with commas.

By doing that, you group by the column values but also by the combination of values across all the grouping columns. If you have value A in column_1 and value B in column_2, this is one group. If there are, for example, values A in column_1 with value C in column_2, this will form another group – despite the values in column_1 being the same as in the earlier example. 

Example

The code below shows the number of albums by the artist and the year of release:

SELECT artist,
	 release_year,
	 COUNT(*) AS number_of_albums
FROM albums
GROUP BY artist, release_year
ORDER BY artist, release_year;

I’m using COUNT(*) to find the number of albums. The output is grouped by artist and release year. As you can see, those are exactly the columns in GROUP BY and they are separated by a comma.

To make the output more readable, I sorted data by artist alphabetically and from the earliest to the latest release year.

This is the output snapshot:

Two Aggregate Functions in One Query

It’s also possible to write a query that has two aggregate functions. No, it’s not what you think: you don’t write one aggregate function within another. That’s not possible in SQL.

Two aggregate functions in one query can mean two things:

1. Simple approach: Using two aggregate functions in two columns of the same SELECT.
2. Advanced approach: Using the result of one aggregate function in another aggregate function.

One Query, Two Aggregate Functions: The Simple Approach

So, the simple approach is when you have two aggregate functions in two different aggregate columns in one SELECT. They can be the same function applied on different columns, different functions used on the same column, or different functions used on different columns. It really doesn’t matter; the point is you have two aggregations in one query. These aggregations don’t ‘communicate’, i.e. they don’t depend on one another in any way.

Example

In the example, I’ll use the table package_deliveries with the script linked here.

The table shows information about the number of packages delivered daily to several cities.

Here are the first six rows in the table:

The code below calculates the total and the average number of packages by city.

SELECT city_of_delivery,
	 SUM(number_of_packages) AS total_number_of_packages,
	 AVG(number_of_packages) AS average_number_of_packages
FROM package_deliveries
GROUP BY city_of_delivery;

You can see the query has two aggregates: one with SUM() and one with AVG(). It really doesn’t matter how many aggregate functions you write in the query. The only important thing is that each aggregation is treated as a separate code line or output column.

Here’s the result:

One Query, Two Aggregate Functions: The Advanced Approach

The advanced version is when the aggregations ‘communicate’, meaning you first need to find one aggregated value and then use it to calculate another aggregated value.  Typically, this is achieved either through subqueries or Common Table Expressions (CTEs).

I’ll show you subquery usage in the example below. Our guide on combining two aggregate functions in GROUP BY covers both approaches.

Example

The following code returns the average daily number of packages delivered in each country:

SELECT country_of_delivery,
	 AVG(pd.daily_number_of_packages) AS average_daily_number_of_packages
FROM (SELECT date_of_delivery, 
	       country_of_delivery,
	 	 SUM(number_of_packages) AS daily_number_of_packages
	FROM package_deliveries
	GROUP BY date_of_delivery, country_of_delivery) AS pd
GROUP BY country_of_delivery;

I first need to find the number of packages delivered daily in each country, which is the first aggregation. The second aggregation uses this result and calculates the daily average by country.

The first aggregation is written in the subquery. It uses the SUM() function and lists the columns date_of_delivery and country_of_delivery in SELECT. The output is grouped by the same columns.

Now, I can write the main query, referencing in AVG() the column daily_number_of_packages from the subquery. I group the output by country and get the average daily number of packages delivered for each country.

GROUP BY with Conditional Aggregates

The CASE or CASE WHEN statement labels data based on its values. This statement can also be used with GROUP BY.

Grouping data by conditional aggregates means using CASE WHEN with GROUP BY. However – and this is important – the CASE WHEN you write in SELECT also has to appear in GROUP BY.

Example

Using the table from the previous example, I can write a code that categorizes countries into EU and non-EU countries and shows the total number of packages delivered for each category:

SELECT
  CASE 
   WHEN country_of_delivery = 'Turkey' THEN 'Non-EU'
   ELSE 'EU'
  END,
  SUM(number_of_packages) AS total_number_of_packages
FROM package_deliveries
GROUP BY CASE 
   WHEN country_of_delivery = 'Turkey' THEN 'Non-EU'
   ELSE 'EU'
  END;

The CASE statement says that Turkey should be categorized as a non-EU country. This condition is stated in WHEN. The category for all other countries is EU, which is written in ELSE.

The CASE statement starts with CASE (hence the name) and ends with the keyword END.

I use the SUM() function to calculate the total number of packages. To show this value by category, I just copied the whole CASE WHEN statement in GROUP BY.

Here’s the output:

GROUP BY Extensions: ROLLUP, CUBE, GROUPING SETS

The three SQL GROUP BY extensions are:

• ROLLUP – Creates a summary row for the groups listed in GROUP BY.
• CUBE – Creates subtotals for all the combinations of the groups in GROUP BY.
• GROUPING SETS – Aggregates output on multiple levels.

In the example, I’ll show you how GROUPING SETS works. You can learn how the other two extensions work in our GROUP BY extensions guide.

Example

The following code returns the number of packages delivered per country and per date:

SELECT country_of_delivery,
	 date_of_delivery,
	 SUM(number_of_packages) AS total_number_of_packages
FROM package_deliveries
GROUP BY GROUPING SETS(country_of_delivery, date_of_delivery)
ORDER BY country_of_delivery, date_of_delivery;

I start by using SUM() to calculate the number of packages.

In GROUP BY, I write GROUPING SETS with all the aggregation levels inside the parentheses.

The output shows the number of delivered packages by country and by delivery date:

It can help to think of grouping sets as two separate queries. The first query groups by country …

SELECT country_of_delivery,
       NULL AS date_of_delivery,
       SUM(number_of_packages) AS total_number_of_packages
FROM package_deliveries
GROUP BY country_of_delivery
ORDER BY country_of_delivery;

… and returns the first three rows of the previous output:

The second query groups by delivery date …

SELECT NULL AS country_of_delivery,
       date_of_delivery,
       SUM(number_of_packages) AS total_number_of_packages
FROM package_deliveries
GROUP BY date_of_delivery
ORDER BY date_of_delivery;

… and outputs the other three rows of the original result:

In other words, if you merge these two separate grouping queries using UNION …

SELECT country_of_delivery,
       NULL AS date_of_delivery,
       SUM(number_of_packages) AS total_number_of_packages
FROM package_deliveries
GROUP BY country_of_delivery

UNION 

SELECT NULL AS country_of_delivery,
       date_of_delivery,
       SUM(number_of_packages) AS total_number_of_packages
FROM package_deliveries
GROUP BY date_of_delivery
ORDER BY country_of_delivery, date_of_delivery;

… you get exactly the same result as with GROUPING SETS.

Common Mistakes When Using GROUP BY

Confusing ORDER BY and GROUP BY

This is often a matter of not knowing what GROUP BY does.

Let me demonstrate with an example.

Say you want to find the total number of packages by country of delivery.

If you’re not familiar with GROUP BY, you might write a naive solution using ORDER BY.

SELECT date_of_delivery,
	 city_of_delivery,
	 country_of_delivery
FROM package_deliveries
ORDER BY country_of_delivery;

Now that you have the data sorted by country, as shown in the snapshot below, you decide to manually sum the values to get the result by country.

But why would you manually do something that GROUP BY does automatically? Instead of ordering data by country, you should group by it and use SUM() to sum the data:

SELECT country_of_delivery,
	 SUM(number_of_packages) AS total_number_of_packages
FROM package_deliveries
GROUP BY country_of_delivery
ORDER BY country_of_delivery;

You immediately get the output you need:

Read more about the differences between GROUP BY and ORDER BY and how to use GROUP BY and ORDER BY together.

Confusing WHERE and HAVING

Another common mistake is trying to filter aggregated values using WHERE. That is not possible — WHERE is used to filter individual rows before aggregation. On the other hand, HAVING is for filtering groups of rows after aggregation.

Example

If you want to get the average number of packages delivered daily by city and show only those with more than 500 packages, you might decide to write this query:

SELECT city_of_delivery,
	 AVG(number_of_packages) AS average_number_of_packages
FROM package_deliveries
WHERE AVG(number_of_packages) > 500
GROUP BY city_of_delivery;

It will return an error because WHERE doesn’t accept an aggregate function as an argument. (This doesn’t mean you can't ever use WHERE with GROUP BY; you can, but not to filter groups.)

In this case, you should use HAVING:

SELECT city_of_delivery,
	 AVG(number_of_packages) AS average_number_of_packages
FROM package_deliveries
GROUP BY city_of_delivery
HAVING AVG(number_of_packages) > 500;

It’s the same query with AVG() and GROUP BY. The only difference is HAVING, where you compare the AVG() calculation with 500.

The output shows only cities whose daily average is above 500.

Learn more about this in the article about HAVING vs. WHERE.

Listing Unaggregated Columns in SELECT but Not in WHERE

The simple rule you should remember is whatever unaggregated column you write in SELECT should also be included in GROUP BY. If you don’t do that, the database won’t know which value to show if several different values are within the same group.

Example

For example, you write the query that is supposed to calculate the total number of packages by country and each city.

SELECT country_of_delivery,
       city_of_delivery,
	 SUM(number_of_packages) total_number_of_packages
FROM package_deliveries
GROUP BY country_of_delivery;

Sure, you have country and city in SELECT, but you forgot to put the city in GROUP BY. This query won’t run. It will return an error. While the messages are worded differently in PostgreSQL, Oracle, and other databases, their message is the same: the column city_of_delivery must appear in GROUP BY.

Even if it ran, the database would be confused. It would think something like this: “You want to show cities but don’t want to group by city? How am I supposed to know which city to show in the output when every country has three of them? Please put cities in GROUP BY so I can show each city as a separate group.”

So, you help it by including the city of delivery in GROUP BY:

SELECT country_of_delivery,
       city_of_delivery,
	 SUM(number_of_packages) total_number_of_packages
FROM package_deliveries
GROUP BY country_of_delivery, city_of_delivery;

Yes, it’s grouping by two columns and you’ve already learned how to do that. Here’s the query’s output:

Other Errors

There are also some other errors, such as forgetting GROUP BY with aggregate functions or not grouping by a unique key. Those and several more are covered in this article about common GROUP BY errors.

Further SQL GROUP BY Resources

After reading this overview of the SQL GROUP BY clause, you should have an idea of its uses and challenges. But if you want to master GROUP BY, you’ll need to go into more detail.

The best way to do that is by checking out some of our courses and cookbooks. Here are some of my suggestions for further learning:

• Creating Basic SQL Reports – This intermediate-level course focuses on the nuances of using GROUP BY – grouping by multiple columns, using HAVING, combining GROUP BY with CASE WHEN, the difference between COUNT(*) and COUNT(id), etc.
• GROUP BY Extensions in SQL – This course covers CUBE, ROLLUP, and GROUPING SETS.
• SQL Practice – The courses in this track have sections dedicated to different SQL topics, including quite a lot of GROUP BY practice.

And don’t forget about our articles featuring GROUP BY examples. Two of my favorites are SQL Practice: 10 GROUP BY Practice Exercises with Detailed Solutions and Top 9 SQL GROUP BY Interview Questions.

In addition to that, there’s an SQL for Data Analysis Cheat Sheet, which has a section dedicated to GROUP BY.

Happy learning!