Oracle Grouping with the ROLLUP Operation
Grouping and summing at multiple levels often takes an application to perform. Take a look at the ROLLUP operation within Oracle and perform these operations in a single SQL statement.
Grouping and in particular gathering aggregates across groups often brings confusion to many practitioners. This does not need to be the case if approached from a systematic fashion. This article will approach gathering aggregates from a simple GROUP BY operation and then extend into Oracle’s higher level grouping operations. In particular, the ROLLUP operation, which allows us to group and aggregate at different levels in a collection of similar rows. By similar I mean that two or more rows must have a common column or set of columns so that we can group them together for performing some form of aggregate function. In this article, I will use the SUM function for aggregating.
Once again, in this article, we revisit the DOG_ORIGIN table. This time however, I have added a new column entitled ‘POPULATION’ that holds a fictitious value for the number of a particular breed in their country of origin. These are just made up numbers so please do not hold me to them. To introduce this table again, Table 1. has the values as I have entered them.
TABLE 1.
DOG_ORIGIN table
| Country |
Breed |
Breed_size |
Population |
| Germany |
German Shepherd Dog |
Big |
12000 |
| Germany |
Dobermann |
Big |
8000 |
| Germany |
Rottweiler |
Big |
9000 |
| USA |
Siberian Husky |
Medium |
5000 |
| USA |
Alaskan Malamute |
Medium |
3000 |
| USA |
American Bulldog |
Big |
8000 |
| Switzerland |
Bernese Mountain Dog |
Big |
2000 |
| Switzerland |
Saint Bernard Dog |
Big |
2000 |
| Switzerland |
Entlebuch Cattle Dog |
Medium |
2000 |
| Australia |
Australian Cattle Dog |
Medium |
6000 |
| Australia |
Jack Russell Terrier |
Small |
7000 |
Early practitioners of SQL often are only introduced to answering questions like ‘How many dogs are in America?‘ or ‘How many dogs are there in the world?‘. These two answers can quickly be answered by the following SQL. These two SQL statements simply investigate each row in the DOG_ORIGIN table and add together the population. In the case where country = ‘USA’ only the dogs in the USA are added together. For the second SQL statement, every row is added together, to give us the grand total of all dogs in the world.
SQL > SELECT SUM(population) FROM dog_origin WHERE country = 'USA';
SUM(POPULATION)
---------------
16000
SQL > SELECT SUM(population) FROM dog_origin;
SUM(POPULATION)
---------------
64000
Over time we soon learn that in order to gather aggregate values for each of the groupings we are interested in, in this case the country column, we can use the GROUP BY operation. This allows us to process and aggregate all grouping of a column without having to issue a separate SQL statement for each grouping we are interested in. Most of us have already used this in the past but it is introduced here because the ROLLUP operation is an extension of the GROUP BY statement. In TABLE 2., the simple question and answer of ‘How many dogs are there within each country?‘ has been given. This can quickly be accomplished by using the simple GROUP BY operation on the country column and then adding the SUM function around the population column. This SQL statement groups all the rows within each country and ‘rolls’ that value up into a single result row that has the accumulated population for that country.
Table 2.
Simple GROUP BY operation
Simple GROUP BY operation
SQL> select country,sum(population)
from dog_origin
group by country;
COUNTRY SUM(POPULATION)
--------------- ---------------
Australia 13000 <- group by country
Germany 29000 <- group by country
Switzerland 6000 <- group by country
USA 16000 <- group by country
Since the ROLLUP operation is just an extension of the GROUP BY operation we should be able to compose a statement that uses the ROLLUP operation and get an answer that resembles our previous simple GROUP BY operation. To understand how we might be able to do this we must first look at the ROLLUP option and its’ syntax.
The ROLLUP operation works on a set of columns you want to group. Just like the GROUP BY operation but aggregates a summary row for each group of columns supplied in its clause. From the most detailed to a grand total and has the following basic syntax format of:
GROUP BY ROLLUP ([columns of interest separated by commas])
The best way to explain how this will work is to supply a simple example. If you had 4 columns of interest in you ROLLUP operation say (c1, c2, c3, c4) Oracle will actually produce 5 different groupings (one more than the number of columns you provide). You can almost think of this as Oracle performing 5 different GROUP BY operations on your behalf.
Those grouping would be:
(c1, c2, c3, c4)
(c1, c2, c3)
(c1, c2)
(c1)
(t1) where t1 is a GROUP BY across all rows
So if we translate the previous simple GROUP BY operation into a ROLLUP operation the statement becomes one like that in TABLE 3. As you can see we have basically the same result set but the totals were generated by the ROLLUP operation instead of the GROUP BY. This is noted by the ‘rollup by country’ notation I have added to the result set. The ROLLUP operation also provides a grand total line, which gives us the total dog population across all countries. If we relate this to the column example above we have told Oracle to perform a GROUP BY on (c1)country and (t1)grand total.
TABLE 3.
Simple ROLLUP operation to mimic a simple GROUP BY operation
SQL> select country,sum(population)
from dog_origin
group by rollup (country);
COUNTRY SUM(POPULATION)
----------- ---------------
Australia 13000 <- rollup by country
Germany 29000 <- rollup by country
Switzerland 6000 <- rollup by country
USA 16000 <- rollup by country
64000 <- grand total given
To go to the next level and show the real power of the ROLLUP operation we need to introduce at least one more column in the grouping. To do this we will add in the BREED_SIZE. The question now becomes ‘What is the population of dogs given the different breed sizes?‘. This is now a very simple SQL statement where all we need to do is add BREED_SIZE into the ROLLUP operation. Table 4. shows the SQL statement and the result set. Oracle now performs GROUP BY operations on (c1,c2)country & breed size, (c1)country, and (t1)grand total. I have added a rollup comment on the end of each row for clarification. So now, we can see the population by dog size in each country as well as the total number of dogs in each country and in the world.
Table 4.
Show population across breed size
SQL> select country,breed_size,sum(population)
from dog_origin
group by rollup (country,breed_size);
COUNTRY BREED_SIZE SUM(POPULATION)
--------------- ---------- ---------------
Australia Medium 6000 <- rollup by country & breed_size
Australia Small 7000 <- rollup by country & breed_size
Australia 13000 <- rollup by country
Germany Big 29000 <- rollup by country & breed_size
Germany 29000 <- rollup by country
Switzerland Big 4000 <- rollup by country & breed_size
Switzerland Medium 2000 <- rollup by country & breed_size
Switzerland 6000 <- rollup by country
USA Big 8000 <- rollup by country & breed_size
USA Medium 8000 <- rollup by country & breed_size
USA 16000 <- rollup by country
64000 <- grand total line
Another hybrid of the GROUP BY..ROLLUP operation is the ability to do partial rollups. The GROUP BY…ROLLUP clause actually has the following syntax. You can move columns between the GROUP BY list and the ROLLUP list to produce different, or partial, aggregated values.
GROUP BY (column[s]) ROLLUP (column[s])
So if we extend our previous column example where we used (c1,c2,c3,c4) we actually have at our disposal the following options when developing our SQL statement. Notice that as we move columns from the ROLLUP list, in the end, all we have left is a simple GROUP BY operation.
Those grouping would be:
group by rollup (c1,c2,c3,c4) - full rollup option
group by c1,rollup (c2,c3,c4) - removes grand total line (t1)
group by c1,c2,rollup (c3,c4) - removes (t1) and group by (c1)
group by c1,c2,c3,rollup (c4) - removes (t1) and group by (c1) & (c1,c2)
group by (c1,c2,c3,c4) - removes (t1) and group by (c1),(c1,c2),&(c1,c2,c3)
For instance if we wanted to remove the grand total line from our dog origin example and only wanted to show detail for the dog population by country and breed size we would have the following SQL in Table 5.
Table 5.
Removal of grand total line
SQL > select country,breed_size,sum(population)
from dog_origin
group by country, rollup (breed_size);
COUNTRY BREED_SIZE SUM(POPULATION)
--------------- ---------- ---------------
Australia Medium 6000
Australia Small 7000
Australia 13000
Germany Big 29000
Germany 29000
Switzerland Big 4000
Switzerland Medium 2000
Switzerland 6000
USA Big 8000
USA Medium 8000
USA 16000
Oracle Logbook 