Just SQL PartVIII

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

Moreover,
if we wanted to get rid of the total line for the dog population in each of the
countries we would have the SQL in Table 6. Here we now just have a
simple GROUP BY operation and have in essence wrapped around to the beginning
of this article.

Table
6.


Simple
GROUP BY operation by country, breed_size


SQL > select country,breed_size,sum(population)
from dog_origin
group by country,breed_size;
COUNTRY BREED_SIZE SUM(POPULATION)
————— ———- —————
Australia Medium 6000
Australia Small 7000
Germany Big 29000
Switzerland Big 4000
Switzerland Medium 2000
USA Big 8000
USA Medium 8000

The ROLLUP
extension to the GROUP by operation is very powerful in producing various
levels of aggregated detail. We can move columns between the GROUP BY list and
the ROLLUP list to fine-tune those aggregated rows we wish to see.

»


See All Articles by Columnist
James Koopmann

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles