SQL GROUP BY and HAVING: The Complete Guide

The SQL GROUP BY clause and SQL HAVING clause are powerful clauses, especially when analysing large amounts of data. Learn what the GROUP BY and HAVING clause is and everything you can do with it in this article.

This article applies to Oracle, SQL Server, MySQL, and PostgreSQL.

What Is The SQL GROUP BY Clause?

The GROUP BY clause is a clause in the SELECT statement. It allows you to create groups of rows that have the same value when using some functions (such as SUM, COUNT, MAX, MIN, and AVG).

Aggregate functions without a GROUP BY will return a single value.

If you want to find the aggregate value for each value of X, you can GROUP BY x to find it.

It allows you to find the answers to problems such as:

• The total number of sales per month
• The average salary of an employee per department
• The number of students enrolled per year

It goes towards the end of your SELECT query.

The SELECT statement could look like this:

SELECT columns
FROM table
WHERE condition
GROUP BY columns
HAVING condition
ORDER BY columns

It allows you to write queries like “select * from table group by column”.

The GROUP BY clause in the query is what we will be discussing in this article. We’ll also cover the HAVING clause as they are closely related.

Sample Data

For our examples in this article, we’ll create a sample table with some data.

This table captures grades that students have received over several years:

• student_name: the name of a student
• subject_name: the subject that the student took
• school_year: the year the subject was taken
• student_grade: the grade the student got in this subject for this year.

Here’s the sample data. You can download this script from my GitHub repository here.

Oracle

CREATE TABLE sample_group_table (
  student_name VARCHAR2(50),
  subject_name VARCHAR2(100),
  school_year NUMBER,
  student_grade NUMBER
);

SQL Server, MySQL, Postgres

CREATE TABLE sample_group_table (
  student_name VARCHAR(50),
  subject_name VARCHAR(100),
  school_year INT,
  student_grade INT
);

Insert (all vendors)

INSERT INTO sample_group_table (student_name, subject_name, school_year, student_grade)
VALUES ('Andrew', 'Physics', 2020, 76);
INSERT INTO sample_group_table (student_name, subject_name, school_year, student_grade)
VALUES ('Belle', 'Physics', 2020, 85);
INSERT INTO sample_group_table (student_name, subject_name, school_year, student_grade)
VALUES ('Chris', 'Physics', 2020, 40);
INSERT INTO sample_group_table (student_name, subject_name, school_year, student_grade)
VALUES ('Debbie', 'Physics', 2020, 54);
INSERT INTO sample_group_table (student_name, subject_name, school_year, student_grade)
VALUES ('Andrew', 'English', 2020, 96);
INSERT INTO sample_group_table (student_name, subject_name, school_year, student_grade)
VALUES ('Belle', 'English', 2020, 99);
INSERT INTO sample_group_table (student_name, subject_name, school_year, student_grade)
VALUES ('Chris', 'English', 2020, 41);
INSERT INTO sample_group_table (student_name, subject_name, school_year, student_grade)
VALUES ('Debbie', 'English', 2020, 49);
INSERT INTO sample_group_table (student_name, subject_name, school_year, student_grade)
VALUES ('Andrew', 'Mathematics', 2020, 44);
INSERT INTO sample_group_table (student_name, subject_name, school_year, student_grade)
VALUES ('Belle', 'Mathematics', 2020, 42);
INSERT INTO sample_group_table (student_name, subject_name, school_year, student_grade)
VALUES ('Chris', 'Mathematics', 2020, 94);
INSERT INTO sample_group_table (student_name, subject_name, school_year, student_grade)
VALUES ('Debbie', 'Mathematics', 2020, 57);
INSERT INTO sample_group_table (student_name, subject_name, school_year, student_grade)
VALUES ('Andrew', 'Physics', 2021, 53);
INSERT INTO sample_group_table (student_name, subject_name, school_year, student_grade)
VALUES ('Belle', 'Physics', 2021, 85);
INSERT INTO sample_group_table (student_name, subject_name, school_year, student_grade)
VALUES ('Chris', 'Physics', 2021, 74);
INSERT INTO sample_group_table (student_name, subject_name, school_year, student_grade)
VALUES ('Debbie', 'Physics', 2021, 72);
INSERT INTO sample_group_table (student_name, subject_name, school_year, student_grade)
VALUES ('Andrew', 'English', 2021, 48);
INSERT INTO sample_group_table (student_name, subject_name, school_year, student_grade)
VALUES ('Belle', 'English', 2021, 76);
INSERT INTO sample_group_table (student_name, subject_name, school_year, student_grade)
VALUES ('Chris', 'English', 2021, 86);
INSERT INTO sample_group_table (student_name, subject_name, school_year, student_grade)
VALUES ('Debbie', 'English', 2021, 68);
INSERT INTO sample_group_table (student_name, subject_name, school_year, student_grade)
VALUES ('Andrew', 'Mathematics', 2021, 41);
INSERT INTO sample_group_table (student_name, subject_name, school_year, student_grade)
VALUES ('Belle', 'Mathematics', 2021, 57);
INSERT INTO sample_group_table (student_name, subject_name, school_year, student_grade)
VALUES ('Chris', 'Mathematics', 2021, 92);
INSERT INTO sample_group_table (student_name, subject_name, school_year, student_grade)
VALUES ('Debbie', 'Mathematics', 2021, 65);

Now we have our sample data, let’s take a look at some examples.

Examples of Aggregate Functions with SQL GROUP BY

Let’s see some examples of aggregate functions with the SQL GROUP BY clause.

Example 1 – COUNT

Let’s say we want to find out the COUNT of student grade records per year.

Our query would need to show the year, and the COUNT of each year.

SELECT school_year,
COUNT(*)
FROM sample_group_table
GROUP BY school_year;

We put the column school_year into the GROUP BY clause because we want to show the COUNT result for each instance of the school_year.

Also, when we use aggregate functions, we need to add any non-aggregate columns into the GROUP BY. Otherwise, we’ll get an error.

So, let’s run this query:

It shows us that there are 2 rows here. We have a COUNT of each school_year value, and it shows different numbers in each year.

Example 2 – MAX

What if we wanted to find the maximum score for each subject, regardless of the student or year?

We would show the subject ID and the MAX function.

SELECT subject_name,
MAX(student_grade) AS max_score
FROM sample_group_table
GROUP BY subject_name;

Results:

We can see the highest grade value for each subject.

However, it’s not in any order.

We can order by either the subject_named or the student_grade.

Let’s order by the maximum student_grade.

SELECT subject_name,
MAX(student_grade) AS max_score
FROM sample_group_table
GROUP BY subject_name
ORDER BY MAX(student_grade) DESC;

Result:

You can see that English has the highest score, and Physics has the lowest score.

Example 3 – AVG

You can do the same kind of query with MIN or AVG or SUM.

Let’s try an example using AVG. Let’s find the average student_grade per student, regardless of subject and year. We’ll order this by the student_name.

SELECT student_name,
AVG(student_grade) AS avg_grade
FROM sample_group_table
GROUP BY student_name
ORDER BY student_name;

Result:

You can see that AVG also works with the GROUP BY clause.

Example 4 – Multiple GROUP BY Columns

We can also use more than one column in the GROUP BY clause.

Let’s say we wanted to see the SUM of all student grades for all years.

Our query would look like this:

SELECT student_name,
school_year,
SUM(student_grade) AS total_grades
FROM sample_group_table
GROUP BY student_name, school_year
ORDER BY student_name, school_year;

Results:

It shows the student name, the school year, and the SUM of the student grade for those values.

What Does GROUP BY 1 Mean?

This means that the grouping should be done on the first column specified in the SELECT clause.

Here’s an example:

SELECT
student_year,
COUNT(*)
FROM sample_group_table
GROUP BY 1;

This query will group the results based on the student_year column.

You can use numbers other than 1: you can GROUP BY 2 if you have at least two columns, or GROUP BY 1, 2 to use groups on multiple columns.

This only works in some databases, such as MySQL. In Oracle, it won’t work as expected, because 1 is treated as a constant value.

What Is the SQL HAVING Clause?

The SQL HAVING clause is a clause in SQL that lets you filter data after it has been grouped with the GROUP BY clause.

If you want to filter data in your SQL query to remove rows based on certain criteria, you would use the WHERE clause.

However, you can’t use the WHERE clause with data from aggregate functions.

This is because the database processes the WHERE clause first, then the GROUP BY. It can’t “go back” and run another WHERE clause against the result of a COUNT function, for example.

To filter data after it has been grouped, you can use the HAVING clause.

The SQL HAVING clause works in a similar way to the WHERE clause.

You specify the conditions after the word HAVING, and any records that meet these criteria after the GROUP BY has been performed will be shown, and all others will not be shown.

Example 1 – AVG

Let’s say we have our average student grade per student. And let’s say we want to only show students where their average is above 52.

We would use the HAVING clause.

SELECT student_name,
AVG(student_grade) AS avg_grade
FROM sample_group_table
GROUP BY student_name
HAVING AVG(student_grade) >= 65
ORDER BY student_name;

Results:

You can see there are a few records here, and these are the only students with an average grade of 65 or over.

Notice that we had to use the AVG function in its complete form in the HAVING clause, and we couldn’t use the column alias. This is because the HAVING clause cannot accept column aliases, because the SELECT clause is run last.

If we were to run a query that used the column alias in Oracle SQL:

SELECT student_name,
AVG(student_grade) AS avg_grade
FROM sample_group_table
GROUP BY student_name
HAVING avg_grade >= 65
ORDER BY student_name;

Result:

ORA-00904: "AVG_GRADE": invalid identifier
00904. 00000 -  "%s: invalid identifier"

This ORA-00904 error means we can’t use the alias in the HAVING clause.

Example 2 – SUM

We can build on another example and filter it using HAVING.

In an earlier example, we displayed the student’s name, school year, and the SUM of their grades.

Let’s say we wanted to see this data, but only for students that had a total of 200 or more.

SELECT student_name,
school_year,
SUM(student_grade) AS total_grades
FROM sample_group_table
GROUP BY student_name, school_year
HAVING SUM(student_grade) >= 200
ORDER BY student_name, school_year;

Result:

This shows how we can use the HAVING clause with a SUM function and multiple columns.

GROUP BY ROLLUP

Available in: Oracle, SQL Server, MySQL, and Postgres.

The SQL GROUP BY clause has more to it than just specifying columns to group by.

There are several different grouping options you can use, and one of them is ROLLUP.

The ROLLUP SQL grouping type allows you to group by subtotals and a grand total.

It looks like this:

SELECT student_name,
school_year,
AVG(student_grade) AS avg_grades
FROM sample_group_table
GROUP BY ROLLUP (student_name, school_year)
ORDER BY student_name, school_year;

This will create grouping levels for:

• Each combination of student_id and school_year
• A subtotal for each student_id
• An overall total

So, if the rollup was like this:

ROLLUP (A, B)

Your groups would be:

• a, b
• a
• overall

If we run this query above, we get these results:

You can see that there is an average for each combination of student_name and school_year.

There is also a row that has a student_name and a NULL value for the school_year. This indicates that it is the subtotal for all school_year values for that student_name.

At the bottom, there is a row where all the values are NULL, which is the overall total.

How can you tell which rows are the subtotals and which rows indicate where the raw data is NULL? Looking at this, you can’t. But we’ll address that issue later in this article.

GROUP BY CUBE in SQL

Available in: Oracle, SQL Server, and Postgres (not MySQL).

Another type of SQL GROUP BY you can do is GROUP BY CUBE.

It’s similar to ROLLUP in SQL but allows for more subtotals to be shown.

If we have the same query as before, we can replace ROLLUP with CUBE.

SELECT student_name,
school_year,
AVG(student_grade) AS avg_grades
FROM sample_group_table
GROUP BY CUBE (student_name, school_year)
ORDER BY student_name, school_year;

Cube in SQL will show subtotals for all combinations of columns.

Using the columns above, it will show:

• Each combination of student_name and school_year (same as ROLLUP)
• A subtotal for each student_name (same as ROLLUP)
• A subtotal for each school_year
• An overall total (same as ROLLUP)

It results in more subtotals, and if you’re after subtotals for every combination, it can be useful.

If we run this query above, we get these results:

You can see that the results are similar, but there are subtotals for school_year values.

If you have a GROUP BY CUBE with three columns, you’ll get even more combinations.

GROUP BY GROUPING SETS in SQL

Available in: Oracle, SQL Server, and Postgres (not MySQL).

Another way to group columns is to use the GROUPING SETS keyword.

If you don’t need to get subtotals for all possible combinations, but you need more than just a standard GROUP BY, you can use the GROUPING SETS keyword.

GROUPING SETS in SQL allows you to be very specific about how you want your data grouped.

You can specify columns, in sets of brackets, that make up the groups.

For example, let’s say we wanted to see the student name, subject name, school year, and a total of the grade.

If we use GROUP BY CUBE, we will get:

• Groups for each combination of the student name, subject name, and school year
• Subtotals for each student name and subject name
• Subtotals for each student name and school year
• Subtotals for each student name
• Subtotals for each subject name and school year
• Subtotals for each subject name
• Subtotals for each school year
• An overall subtotal

As you can see, this is a lot of totals to display.

If we don’t need them all, we can use GROUP BY GROUPING SETS to specify what to show.

We could say that we only want to see subtotals of student name and subject name, and then subtotals of school_year.

Our query would look like this:

SELECT student_name,
subject_name,
school_year,
SUM(student_grade) AS total_grades
FROM sample_group_table
GROUP BY GROUPING SETS ((student_name, subject_name), school_year)
ORDER BY student_name, subject_name, school_year;

Results:

It shows groups for student name and subject name, and NULL for school year – indicating this is a subtotal.

It then shows a subtotal for each school year value, at the bottom.

There is no overall total. But we can add one with ().

SELECT student_name,
subject_name,
school_year,
SUM(student_grade) AS total_grades
FROM sample_group_table
GROUP BY GROUPING SETS ((student_name, subject_name), school_year, ())
ORDER BY student_name, subject_name, school_year;

Results:

You can see the results are the same, but there is now an overall total.

So, the GROUPING SETS option is quite flexible in what you want to display.

Composite Grouping

With the different ways to group data, you can be pretty flexible with what you want to display.

If you use ROLLUP, CUBE, or GROUPING SETS, you can use brackets within the grouping criteria to further define how you want to group data.

Let’s say you have a query that does a ROLLUP:

ROLLUP (a, b, c, d)

This will create groups and subtotals for:

• a, b, c, d
• a, b, c
• a, b
• a
• Overall

If you use CUBE:

CUBE (a, b, c, d)

You will get groups and subtotals for:

• a, b, c, d
• a, b, c
• a, b, d
• a, c, d
• a, b, d
• a, b
• a, c
• a, d
• a
• b, c, d
• b, c
• b, d
• b
• c, d
• c
• d
• Overall

Composite grouping allows you to add columns together, within brackets, inside the CUBE or ROLLUP brackets. This means they are treated as a single unit.

For example:

ROLLUP ((a, b), c, d)

This will produce groups of:

• a, b, c, d
• a, b, c
• a, b
• ()

There is no group made for “a”, because it is combined with “b” to make a single group.

You can use a similar concept with CUBE:

CUBE (a, (b, c), d)

This will produce groups of:

• a, b, c, d
• a, b, c
• a, d
• a
• b, c, d
• b, c
• d
• Overall

It results in a much smaller set of groups.

GROUPING Function

Available in: Oracle, SQL Server, MySQL, and Postgres.

There are several SQL functions related to SQL GROUP BY and the grouping feature that can help you identify and work with groups.

Earlier in our examples, we noticed that subtotals had values of NULL for the columns that weren’t being considered in the group.

How can we tell which values are subtotals and which values are NULL because of the underlying data?

This is what the GROUPING function can help with.

The GROUPING function takes a single column as a parameter. The output is either 1 or 0:

• It returns 1 if the column is being used as part of a subtotal and is showing NULL.
• It returns 0 if the underlying value is NULL or any other value.

If we use an earlier example and add the GROUPING function, we can see which rows are the subtotal rows:

SELECT student_name,
school_year,
SUM(student_grade) AS total_grade,
GROUPING(student_name) AS grp_stud,
GROUPING(school_year) AS grp_yr
FROM sample_group_table
GROUP BY ROLLUP (student_name, school_year)
ORDER BY student_name, school_year;

Results:

You can see that there are several values in the GRP_STUD column and the GRP_YR column that have a value of 1. This means that they are subtotal rows.

You can then use other programs or code to treat these rows differently if you need to. It’s much more reliable than just checking if the other columns are NULL.

You can also order by these columns, to have the subtotals at the start or the end, instead of in the middle of the results.

SELECT student_name,
school_year,
SUM(student_grade) AS total_grade,
GROUPING(student_name) AS grp_stud,
GROUPING(school_year) AS grp_yr
FROM sample_group_table
GROUP BY ROLLUP (student_name, school_year)
ORDER BY GROUPING(student_name), GROUPING(school_year), student_name, school_year;

Results:

GROUPING_ID Function

Available in: Oracle and SQL Server (not MySQL or Postgres).

The GROUPING_ID function is another function you can use when working with SQL GROUP BY. It can be used to identify:

• Whether the row is a subtotal
• What “level” the subtotal is for

You provide the columns that you’re grouping by as parameters. The GROUPING_ID function returns a number indicating the grouping level:

• 0 for combinations of each column
• 1 for subtotals of column 1
• 2 for subtotals of column 2
• And so on…

The highest value returned is also for the overall total, which will depend on how many groups you have.

SQL Server documentation here also mentions how it’s different to their GROUPING function.

For example:

SELECT student_name,
school_year,
SUM(student_grade) AS total_grade,
GROUPING_ID(student_name, school_year) AS grp_id
FROM sample_group_table
GROUP BY CUBE(student_name, school_year)
ORDER BY student_name, school_year;

Results:

You can see the grp_id column shows different values depending on if the row is a subtotal or not.

GROUP_ID Function

Available in: Oracle (not SQL Server, MySQL, or Postgres).

The GROUP_ID function is used to assign a number to each group that results from a SELECT query clause with a GROUP BY clause.

It’s used to identify any duplicate groups in your output and works with queries with aggregate functions.

For each unique group that is returned, GROUP_ID will return 0. If a duplicate is found, a value >0 will be returned.

For example:

SELECT student_name,
school_year,
SUM(student_grade) AS total_grade,
GROUP_ID()
FROM sample_group_table
GROUP BY student_name, CUBE(student_name, school_year)
ORDER BY student_name, school_year;

Results:

You can see that some records here have a GROUP_ID of 1.

Concatenated Grouping

It’s possible to combine different grouping types into one SQL GROUP BY clause.

You can specify a combination of GROUP BY, GROUPING SETS, CUBEs or ROLLUPs in a single SQL GROUP BY clause.

The groups are then concatenated and grouping sets are then made.

For example:

SELECT student_name,
school_year,
SUM(student_grade) AS total_grade
FROM sample_group_table
GROUP BY ROLLUP (student_name, school_year)
ORDER BY student_name, school_year;

This query groups by a rollup of student_name and school_year.

Now, what if we add a regular GROUP BY to that as well?

SELECT student_name,
school_year,
SUM(student_grade) AS total_grade
FROM sample_group_table
GROUP BY student_name, ROLLUP (student_name, school_year)
ORDER BY student_name, school_year;

Results:

This results in extra grouping sets.

Summary of SQL GROUP BY Differences Between Vendors

This table shows the differences in the SQL GROUP BY clause between each database vendor.

Summary

So, in summary:

• Grouping is needed when working with aggregate functions.
• The SQL GROUP BY clause allows you to specify the columns to group by.
• The HAVING clause allows you to filter records after the GROUP BY is applied.
• ROLLUP lets you group by sets of columns, from right to left.
• CUBE lets you group by all combinations of columns.
• GROUPING SETS lets you specify the sets or subtotals you require.
• Composite grouping means you can use brackets to force several columns to be treated as a single unit for grouping.
• Concatenated grouping means you can specify different group types within the same GROUP BY clause, separated by a comma.
• The GROUPING function shows 1 if a row is a subtotal row and 0 if it is not.
• The GROUPING_ID function shows a number indicating what level of a group the row relates to.
• The GROUP_ID function shows 0 if the record is unique or 1 if it is a duplicate.

That’s how the GROUP BY clause and related keywords work.