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

In this article, we will cover:

  • What Is The GROUP BY Clause?
  • Sample Data
  • Examples of Aggregate Functions with GROUP BY
  • Filtering the Grouped Data with the HAVING Clause
  • GROUP BY ROLLUP In Oracle SQL
  • GROUP BY CUBE in Oracle SQL
  • GROUP BY GROUPING SETS In Oracle SQL
  • Composite Grouping
  • GROUPING Function
  • GROUPING_ID Function
  • GROUP_ID Function
  • Concatenated Grouping
  • Summary

 

What Is The GROUP BY Clause?

The GROUP BY clause is a clause in the SELECT statement. It allows you to create groups of values when using aggregating functions.

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

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.

Want an Oracle SQL function cheat sheet for easy reference? Click here to get a PDF file that contains all of Oracle functions for you to reference or print out.

 

Sample Data

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

You can use a combination of DBMS_RANDOM and CONNECT BY to generate random data.

This table captures grades that students have received over several years. The grade is a decimal number, and we’re capturing the ID numbers of the students and subjects.

CREATE TABLE sample_group_table (
  school_year NUMBER,
  student_id NUMBER,
  subject_id NUMBER,
  student_grade NUMBER(5, 2)
);

INSERT INTO sample_group_table (school_year, student_id, subject_id, student_grade)
SELECT
TRUNC(DBMS_RANDOM.value(low => 2013, high => 2016)),
TRUNC(DBMS_RANDOM.value(low => 1, high => 12)),
TRUNC(DBMS_RANDOM.value(low => 1, high => 10)),
ROUND(DBMS_RANDOM.value(low => 1, high => 100), 2)
FROM dual
CONNECT BY level <= 1000;

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

 

Examples of Aggregate Functions with GROUP BY

Let’s see some examples of some aggregate functions with the 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:

SCHOOL_YEAR COUNT(*)
2013 325
2014 349
2015 326

It shows us that there are 3 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_id,
MAX(student_grade) AS max_score
FROM sample_group_table
GROUP BY subject_id;

Results:

SUBJECT_ID MAX_SCORE
1 99.62
6 99.61
2 99
5 99.19
4 99.26
8 98.75
3 98.06
7 99.93
9 98.87

We can see the highest grade value for each subject.

However, it’s not in any order.

We can order by either the subject_id, or the student_grade.

Let’s order by the maximum student_grade.

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

Result:

SUBJECT_ID MAX_SCORE
7 99.93
1 99.62
6 99.61
4 99.26
5 99.19
2 99
9 98.87
8 98.75
3 98.06

You can see that subject ID 8 has the highest score, and subject ID 7 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_id.

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

Result:

STUDENT_ID AVG_GRADE
1 50.93583333
2 52.85352273
3 53.276
4 47.63966292
5 48.47636364
6 51.227
7 53.86227723
8 47.49273684
9 53.02617021
10 50.32844444
11 51.5071875

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 students grades for all years.

Our query would look like this:

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

Results:

STUDENT_ID SCHOOL_YEAR TOTAL_GRADES
1 2013 1455.48
1 2014 1179.21
1 2015 1643.92
2 2013 1495.94
2 2014 1702.22
2 2015 1452.95
3 2013 1589.43
3 2014 1630.19
3 2015 1841.6
4 2013 1236.79
4 2014 1505.89
4 2015 1497.25
5 2013 1416.19
5 2014 1651.4
5 2015 1198.33
6 2013 1328.23
6 2014 1635.57
6 2015 1134.36
7 2013 1621.9
7 2014 2035.9
7 2015 1782.29
8 2013 1137.8
8 2014 1700.64
8 2015 1673.37
9 2013 1920.63
9 2014 1863.64
9 2015 1200.19
10 2013 1666.61
10 2014 1438.98
10 2015 1423.97
11 2013 1580.63
11 2014 1673.48
11 2015 1690.58

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

 

Filtering the Grouped Data with the HAVING 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 Oracle 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 HAVING clause works in a similar way to the WHERE clause.

You specify the conditions after the word HAVING, and any records that meet this 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_id,
AVG(student_grade) AS avg_grade
FROM sample_group_table
GROUP BY student_id
HAVING AVG(student_grade) >= 52
ORDER BY student_id;

Results:

STUDENT_ID AVG_GRADE
2 52.85352273
3 53.276
7 53.86227723
9 53.02617021

You can see there are a few records here, and these are the only students with an average grade of 52 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:

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

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 ID, 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 600 or more.

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

Result:

STUDENT_ID SCHOOL_YEAR TOTAL_GRADES
1 2013 1455.48
1 2014 1179.21
1 2015 1643.92
2 2013 1495.94
2 2014 1702.22
2 2015 1452.95
3 2013 1589.43
3 2014 1630.19
3 2015 1841.6
4 2013 1236.79
4 2014 1505.89
4 2015 1497.25
5 2013 1416.19
5 2014 1651.4
5 2015 1198.33
6 2013 1328.23
6 2014 1635.57
6 2015 1134.36
7 2013 1621.9
7 2014 2035.9
7 2015 1782.29
8 2013 1137.8
8 2014 1700.64
8 2015 1673.37
9 2013 1920.63
9 2014 1863.64
9 2015 1200.19
10 2013 1666.61
10 2014 1438.98
10 2015 1423.97
11 2013 1580.63
11 2014 1673.48
11 2015 1690.58

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

 

GROUP BY ROLLUP In Oracle SQL

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_id,
school_year,
AVG(student_grade) AS avg_grades
FROM sample_group_table
GROUP BY ROLLUP (student_id, school_year)
ORDER BY student_id, 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:

STUDENT_ID SCHOOL_YEAR AVG_GRADES
1 2013 48.516
1 2014 49.13375
1 2015 54.79733333
1 (null) 50.93583333
2 2013 51.58413793
2 2014 58.69724138
2 2015 48.43166667
2 (null) 52.85352273
3 2013 51.27193548
3 2014 49.39969697
3 2015 59.40645161
3 (null) 53.276
4 2013 51.53291667
4 2014 41.83027778
4 2015 51.62931034
4 (null) 47.63966292
5 2013 52.45148148
5 2014 48.57058824
5 2015 44.38259259
5 (null) 48.47636364
6 2013 55.34291667
6 2014 54.519
6 2015 43.62923077
6 (null) 51.227
7 2013 52.31935484
7 2014 59.87941176
7 2015 49.50805556
7 (null) 53.86227723
8 2013 39.23448276
8 2014 51.53454545
8 2015 50.70818182
8 (null) 47.49273684
9 2013 56.48911765
9 2014 54.81294118
9 2015 46.16115385
9 (null) 53.02617021
10 2013 49.01794118
10 2014 47.966
10 2015 54.76807692
10 (null) 50.32844444
11 2013 49.3946875
11 2014 52.29625
11 2015 52.830625
11 (null) 51.5071875
(null) (null) 51.00556

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

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

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 Oracle SQL

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

It’s similar to ROLLUP in Oracle 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_id,
school_year,
AVG(student_grade) AS avg_grades
FROM sample_group_table
GROUP BY CUBE (student_id, school_year)
ORDER BY student_id, school_year;

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

Using the columns above, it will show:

  • Each combination of student_id and school_year (same as ROLLUP)
  • A subtotal for each student_id (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:

STUDENT_ID SCHOOL_YEAR AVG_GRADES
1 2013 48.516
1 2014 49.13375
1 2015 54.79733333
1 (null) 50.93583333
2 2013 51.58413793
2 2014 58.69724138
2 2015 48.43166667
2 (null) 52.85352273
3 2013 51.27193548
3 2014 49.39969697
3 2015 59.40645161
3 (null) 53.276
4 2013 51.53291667
4 2014 41.83027778
4 2015 51.62931034
4 (null) 47.63966292
5 2013 52.45148148
5 2014 48.57058824
5 2015 44.38259259
5 (null) 48.47636364
6 2013 55.34291667
6 2014 54.519
6 2015 43.62923077
6 (null) 51.227
7 2013 52.31935484
7 2014 59.87941176
7 2015 49.50805556
7 (null) 53.86227723
8 2013 39.23448276
8 2014 51.53454545
8 2015 50.70818182
8 (null) 47.49273684
9 2013 56.48911765
9 2014 54.81294118
9 2015 46.16115385
9 (null) 53.02617021
10 2013 49.01794118
10 2014 47.966
10 2015 54.76807692
10 (null) 50.32844444
11 2013 49.3946875
11 2014 52.29625
11 2015 52.830625
11 (null) 51.5071875
(null) 2013 50.61424615
(null) 2014 51.62498567
(null) 2015 50.73254601
(null) (null) 51.00556

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 Oracle SQL

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 Oracle 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 ID, subject ID, school year, and an AVG of the grade.

If we use GROUP BY CUBE, we will get:

  • Groups for each combination of the student ID, subject ID, and school year
  • Subtotals for each student ID and subject ID
  • Subtotals for each student ID and school year
  • Subtotals for each student ID
  • Subtotals for each subject ID and school year
  • Subtotals for each subject ID
  • 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 ID and subject ID, and then subtotals of school_year.

Our query would look like this:

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

Results:

STUDENT_ID SUBJECT_ID SCHOOL_YEAR TOTAL_GRADES
1 1 (null) 324.53
1 2 (null) 337.96
1 3 (null) 580.53
1 4 (null) 599.24
1 5 (null) 416.48
1 6 (null) 375.21
1 7 (null) 402.35
1 8 (null) 518.44
1 9 (null) 723.87
2 1 (null) 389.73
2 2 (null) 360.23
2 3 (null) 536.75
2 4 (null) 653.33
2 5 (null) 424.96
2 6 (null) 450.28
2 7 (null) 535.62
2 8 (null) 706.6
2 9 (null) 593.61
3 1 (null) 657.56
3 2 (null) 1035.52
3 3 (null) 248.39
3 4 (null) 441.32
3 5 (null) 620.95
3 6 (null) 457.43
3 7 (null) 554.82
3 8 (null) 396.05
3 9 (null) 649.18
4 1 (null) 355.36
4 2 (null) 687.64
4 3 (null) 624.78
4 4 (null) 412.19
4 5 (null) 318.31
4 6 (null) 334.57
4 7 (null) 415.91
4 8 (null) 570.98
4 9 (null) 520.19
5 1 (null) 539.81
5 2 (null) 451.71
5 3 (null) 382.12
5 4 (null) 437.65
5 5 (null) 683.88
5 6 (null) 357.87
5 7 (null) 314.93
5 8 (null) 380.62
5 9 (null) 717.33
6 1 (null) 513.61
6 2 (null) 622.74
6 3 (null) 257.64
6 4 (null) 621.51
6 5 (null) 510.28
6 6 (null) 349.56
6 7 (null) 247.56
6 8 (null) 525.26
6 9 (null) 450
7 1 (null) 833.54
7 2 (null) 566.76
7 3 (null) 582.97
7 4 (null) 738.63
7 5 (null) 944.43
7 6 (null) 409.67
7 7 (null) 301.28
7 8 (null) 599.53
7 9 (null) 463.28
8 1 (null) 1019.64
8 2 (null) 536.57
8 3 (null) 337.49
8 4 (null) 484.55
8 5 (null) 309.76
8 6 (null) 701.6
8 7 (null) 410.35
8 8 (null) 298.08
8 9 (null) 413.77
9 1 (null) 492.77
9 2 (null) 1027.5
9 3 (null) 497.84
9 4 (null) 473.89
9 5 (null) 579.51
9 6 (null) 656.75
9 7 (null) 437.81
9 8 (null) 507.82
9 9 (null) 310.57
10 1 (null) 621.87
10 2 (null) 640.61
10 3 (null) 448.57
10 4 (null) 496.4
10 5 (null) 505.89
10 6 (null) 483.01
10 7 (null) 454.69
10 8 (null) 737.64
10 9 (null) 140.88
11 1 (null) 701.42
11 2 (null) 497.23
11 3 (null) 674.54
11 4 (null) 531.65
11 5 (null) 655.58
11 6 (null) 358.36
11 7 (null) 618.88
11 8 (null) 610.54
11 9 (null) 296.49
(null) (null) 2013 16449.63
(null) (null) 2014 18017.12
(null) (null) 2015 16538.81

It shows groups for student ID and subject ID, 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_id,
subject_id,
school_year,
SUM(student_grade) AS total_grades
FROM sample_group_table
GROUP BY GROUPING SETS ((student_id, subject_id), school_year, ())
ORDER BY student_id, subject_id, school_year;

Results:

STUDENT_ID SUBJECT_ID SCHOOL_YEAR TOTAL_GRADES
1 1 (null) 324.53
1 2 (null) 337.96
1 3 (null) 580.53
1 4 (null) 599.24
1 5 (null) 416.48
1 6 (null) 375.21
1 7 (null) 402.35
1 8 (null) 518.44
1 9 (null) 723.87
2 1 (null) 389.73
2 2 (null) 360.23
2 3 (null) 536.75
2 4 (null) 653.33
2 5 (null) 424.96
2 6 (null) 450.28
2 7 (null) 535.62
2 8 (null) 706.6
2 9 (null) 593.61
3 1 (null) 657.56
3 2 (null) 1035.52
3 3 (null) 248.39
3 4 (null) 441.32
3 5 (null) 620.95
3 6 (null) 457.43
3 7 (null) 554.82
3 8 (null) 396.05
3 9 (null) 649.18
4 1 (null) 355.36
4 2 (null) 687.64
4 3 (null) 624.78
4 4 (null) 412.19
4 5 (null) 318.31
4 6 (null) 334.57
4 7 (null) 415.91
4 8 (null) 570.98
4 9 (null) 520.19
5 1 (null) 539.81
5 2 (null) 451.71
5 3 (null) 382.12
5 4 (null) 437.65
5 5 (null) 683.88
5 6 (null) 357.87
5 7 (null) 314.93
5 8 (null) 380.62
5 9 (null) 717.33
6 1 (null) 513.61
6 2 (null) 622.74
6 3 (null) 257.64
6 4 (null) 621.51
6 5 (null) 510.28
6 6 (null) 349.56
6 7 (null) 247.56
6 8 (null) 525.26
6 9 (null) 450
7 1 (null) 833.54
7 2 (null) 566.76
7 3 (null) 582.97
7 4 (null) 738.63
7 5 (null) 944.43
7 6 (null) 409.67
7 7 (null) 301.28
7 8 (null) 599.53
7 9 (null) 463.28
8 1 (null) 1019.64
8 2 (null) 536.57
8 3 (null) 337.49
8 4 (null) 484.55
8 5 (null) 309.76
8 6 (null) 701.6
8 7 (null) 410.35
8 8 (null) 298.08
8 9 (null) 413.77
9 1 (null) 492.77
9 2 (null) 1027.5
9 3 (null) 497.84
9 4 (null) 473.89
9 5 (null) 579.51
9 6 (null) 656.75
9 7 (null) 437.81
9 8 (null) 507.82
9 9 (null) 310.57
10 1 (null) 621.87
10 2 (null) 640.61
10 3 (null) 448.57
10 4 (null) 496.4
10 5 (null) 505.89
10 6 (null) 483.01
10 7 (null) 454.69
10 8 (null) 737.64
10 9 (null) 140.88
11 1 (null) 701.42
11 2 (null) 497.23
11 3 (null) 674.54
11 4 (null) 531.65
11 5 (null) 655.58
11 6 (null) 358.36
11 7 (null) 618.88
11 8 (null) 610.54
11 9 (null) 296.49
(null) (null) 2013 16449.63
(null) (null) 2014 18017.12
(null) (null) 2015 16538.81
(null) (null) (null) 51005.56

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

There are several SQL functions related to grouping 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_id,
school_year,
SUM(student_grade) AS total_grade,
GROUPING(student_id) AS grp_stud,
GROUPING(school_year) AS grp_yr
FROM sample_group_table
GROUP BY ROLLUP (student_id, school_year)
ORDER BY student_id, school_year;

Results:

STUDENT_ID SCHOOL_YEAR TOTAL_GRADE GRP_STUD GRP_YR
1 2013 1455.48 0 0
1 2014 1179.21 0 0
1 2015 1643.92 0 0
1 (null) 4278.61 0 1
2 2013 1495.94 0 0
2 2014 1702.22 0 0
2 2015 1452.95 0 0
2 (null) 4651.11 0 1
3 2013 1589.43 0 0
3 2014 1630.19 0 0
3 2015 1841.6 0 0
3 (null) 5061.22 0 1
4 2013 1236.79 0 0
4 2014 1505.89 0 0
4 2015 1497.25 0 0
4 (null) 4239.93 0 1
5 2013 1416.19 0 0
5 2014 1651.4 0 0
5 2015 1198.33 0 0
5 (null) 4265.92 0 1
6 2013 1328.23 0 0
6 2014 1635.57 0 0
6 2015 1134.36 0 0
6 (null) 4098.16 0 1
7 2013 1621.9 0 0
7 2014 2035.9 0 0
7 2015 1782.29 0 0
7 (null) 5440.09 0 1
8 2013 1137.8 0 0
8 2014 1700.64 0 0
8 2015 1673.37 0 0
8 (null) 4511.81 0 1
9 2013 1920.63 0 0
9 2014 1863.64 0 0
9 2015 1200.19 0 0
9 (null) 4984.46 0 1
10 2013 1666.61 0 0
10 2014 1438.98 0 0
10 2015 1423.97 0 0
10 (null) 4529.56 0 1
11 2013 1580.63 0 0
11 2014 1673.48 0 0
11 2015 1690.58 0 0
11 (null) 4944.69 0 1
(null) (null) 51005.56 1 1

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_id,
school_year,
SUM(student_grade) AS total_grade,
GROUPING(student_id) AS grp_stud,
GROUPING(school_year) AS grp_yr
FROM sample_group_table
GROUP BY ROLLUP (student_id, school_year)
ORDER BY GROUPING(student_id), GROUPING(school_year), student_id, school_year;

Results:

STUDENT_ID SCHOOL_YEAR TOTAL_GRADE GRP_STUD GRP_YR
1 2013 1455.48 0 0
1 2014 1179.21 0 0
1 2015 1643.92 0 0
2 2013 1495.94 0 0
2 2014 1702.22 0 0
2 2015 1452.95 0 0
3 2013 1589.43 0 0
3 2014 1630.19 0 0
3 2015 1841.6 0 0
4 2013 1236.79 0 0
4 2014 1505.89 0 0
4 2015 1497.25 0 0
5 2013 1416.19 0 0
5 2014 1651.4 0 0
5 2015 1198.33 0 0
6 2013 1328.23 0 0
6 2014 1635.57 0 0
6 2015 1134.36 0 0
7 2013 1621.9 0 0
7 2014 2035.9 0 0
7 2015 1782.29 0 0
8 2013 1137.8 0 0
8 2014 1700.64 0 0
8 2015 1673.37 0 0
9 2013 1920.63 0 0
9 2014 1863.64 0 0
9 2015 1200.19 0 0
10 2013 1666.61 0 0
10 2014 1438.98 0 0
10 2015 1423.97 0 0
11 2013 1580.63 0 0
11 2014 1673.48 0 0
11 2015 1690.58 0 0
1 (null) 4278.61 0 1
2 (null) 4651.11 0 1
3 (null) 5061.22 0 1
4 (null) 4239.93 0 1
5 (null) 4265.92 0 1
6 (null) 4098.16 0 1
7 (null) 5440.09 0 1
8 (null) 4511.81 0 1
9 (null) 4984.46 0 1
10 (null) 4529.56 0 1
11 (null) 4944.69 0 1
(null) (null) 51005.56 1 1

 

GROUPING_ID Function

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.

For example:

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

Results:

STUDENT_ID SCHOOL_YEAR TOTAL_GRADE GRP_ID
1 2013 1455.48 0
1 2014 1179.21 0
1 2015 1643.92 0
1 (null) 4278.61 1
2 2013 1495.94 0
2 2014 1702.22 0
2 2015 1452.95 0
2 (null) 4651.11 1
3 2013 1589.43 0
3 2014 1630.19 0
3 2015 1841.6 0
3 (null) 5061.22 1
4 2013 1236.79 0
4 2014 1505.89 0
4 2015 1497.25 0
4 (null) 4239.93 1
5 2013 1416.19 0
5 2014 1651.4 0
5 2015 1198.33 0
5 (null) 4265.92 1
6 2013 1328.23 0
6 2014 1635.57 0
6 2015 1134.36 0
6 (null) 4098.16 1
7 2013 1621.9 0
7 2014 2035.9 0
7 2015 1782.29 0
7 (null) 5440.09 1
8 2013 1137.8 0
8 2014 1700.64 0
8 2015 1673.37 0
8 (null) 4511.81 1
9 2013 1920.63 0
9 2014 1863.64 0
9 2015 1200.19 0
9 (null) 4984.46 1
10 2013 1666.61 0
10 2014 1438.98 0
10 2015 1423.97 0
10 (null) 4529.56 1
11 2013 1580.63 0
11 2014 1673.48 0
11 2015 1690.58 0
11 (null) 4944.69 1
(null) 2013 16449.63 2
(null) 2014 18017.12 2
(null) 2015 16538.81 2
(null) (null) 51005.56 3

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

 

GROUP_ID Function

The GROUP_ID function is another group-related function. This function allows you to identify duplicate subtotal records, which may appear, depending on how you’ve written your query.

It shows 0 for each unique record, and 1 if the record is not unique (a duplicate). There are no parameters for the GROUP_ID function.

For example:

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

Results:

STUDENT_ID SCHOOL_YEAR TOTAL_GRADE GROUP_ID()
1 2013 1455.48 1
1 2013 1455.48 0
1 2014 1179.21 0
1 2014 1179.21 1
1 2015 1643.92 0
1 2015 1643.92 1
1 (null) 4278.61 1
1 (null) 4278.61 0
2 2013 1495.94 0
2 2013 1495.94 1
2 2014 1702.22 1
2 2014 1702.22 0
2 2015 1452.95 0
2 2015 1452.95 1
2 (null) 4651.11 1
2 (null) 4651.11 0
3 2013 1589.43 1
3 2013 1589.43 0
3 2014 1630.19 1
3 2014 1630.19 0
3 2015 1841.6 1
3 2015 1841.6 0
3 (null) 5061.22 0
3 (null) 5061.22 1
4 2013 1236.79 0
4 2013 1236.79 1
4 2014 1505.89 0
4 2014 1505.89 1
4 2015 1497.25 0
4 2015 1497.25 1
4 (null) 4239.93 0
4 (null) 4239.93 1
5 2013 1416.19 0
5 2013 1416.19 1
5 2014 1651.4 0
5 2014 1651.4 1
5 2015 1198.33 0
5 2015 1198.33 1
5 (null) 4265.92 1
5 (null) 4265.92 0
6 2013 1328.23 0
6 2013 1328.23 1
6 2014 1635.57 0
6 2014 1635.57 1
6 2015 1134.36 1
6 2015 1134.36 0
6 (null) 4098.16 1
6 (null) 4098.16 0
7 2013 1621.9 1
7 2013 1621.9 0
7 2014 2035.9 0
7 2014 2035.9 1
7 2015 1782.29 0
7 2015 1782.29 1
7 (null) 5440.09 1
7 (null) 5440.09 0
8 2013 1137.8 0
8 2013 1137.8 1
8 2014 1700.64 0
8 2014 1700.64 1
8 2015 1673.37 1
8 2015 1673.37 0
8 (null) 4511.81 1
8 (null) 4511.81 0
9 2013 1920.63 1
9 2013 1920.63 0
9 2014 1863.64 0
9 2014 1863.64 1
9 2015 1200.19 0
9 2015 1200.19 1
9 (null) 4984.46 1
9 (null) 4984.46 0
10 2013 1666.61 1
10 2013 1666.61 0
10 2014 1438.98 1
10 2014 1438.98 0
10 2015 1423.97 0
10 2015 1423.97 1
10 (null) 4529.56 0
10 (null) 4529.56 1
11 2013 1580.63 1
11 2013 1580.63 0
11 2014 1673.48 1
11 2014 1673.48 0
11 2015 1690.58 1
11 2015 1690.58 0
11 (null) 4944.69 1
11 (null) 4944.69 0

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

 

Concatenated Grouping

It’s possible to combine different grouping types into the 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_id,
school_year,
SUM(student_grade) AS total_grade
FROM sample_group_table
GROUP BY ROLLUP (student_id, school_year)
ORDER BY student_id, school_year;

This query groups by a rollup of student_id and school_year.

Now, what if we add a regular group by to that as well?

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

Results:

STUDENT_ID SCHOOL_YEAR TOTAL_GRADE
1 2013 1455.48
1 2014 1179.21
1 2015 1643.92
1 (null) 4278.61
1 (null) 4278.61
2 2013 1495.94
2 2014 1702.22
2 2015 1452.95
2 (null) 4651.11
2 (null) 4651.11
3 2013 1589.43
3 2014 1630.19
3 2015 1841.6
3 (null) 5061.22
3 (null) 5061.22
4 2013 1236.79
4 2014 1505.89
4 2015 1497.25
4 (null) 4239.93
4 (null) 4239.93
5 2013 1416.19
5 2014 1651.4
5 2015 1198.33
5 (null) 4265.92
5 (null) 4265.92
6 2013 1328.23
6 2014 1635.57
6 2015 1134.36
6 (null) 4098.16
6 (null) 4098.16
7 2013 1621.9
7 2014 2035.9
7 2015 1782.29
7 (null) 5440.09
7 (null) 5440.09
8 2013 1137.8
8 2014 1700.64
8 2015 1673.37
8 (null) 4511.81
8 (null) 4511.81
9 2013 1920.63
9 2014 1863.64
9 2015 1200.19
9 (null) 4984.46
9 (null) 4984.46
10 2013 1666.61
10 2014 1438.98
10 2015 1423.97
10 (null) 4529.56
10 (null) 4529.56
11 2013 1580.63
11 2014 1673.48
11 2015 1690.58
11 (null) 4944.69
11 (null) 4944.69

This results in extra grouping sets.

 

Summary

So, in summary:

  • Grouping is needed when working with aggregate functions.
  • The 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 your 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 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.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Want an Oracle SQL function cheat sheet for easy reference? Click here to get a PDF file that contains all of Oracle functions for you to reference or print out.

Image courtesy of cooldesign / FreeDigitalPhotos.net

Improve Your Oracle SQL With My 10-Day Email Course

x