Group by rollup

This week I teached a course about the Do's and Don'ts in Oracle 10gR2 (SQL and PL/SQL) I had a room full of experienced Oracle Oracle 7 and Oracle 8 programmers.

I talked about grouping and the rollup function in SQL.
One of the questions was: How can we use the grouping totals in our reporting tool.

A solution:

Let's look at the salaries per job per department in our emp table:

What if I want the following results in a report:
- How much do all SALESMAN earn in department 30?
- How much do all employees earn in total?
By using rollup in combination with the grouping function we came up with the following query

This query results in the following output: Note the grouping function.
It shows the level of the group by
0: No grouping at this level
1: Grouping on this level

Now we can anwer the questions.
- How much do all SALESMAN earn in department 30? Answer: 5600
- How much do all employees earn in total? Answer: 27725

How can I refer to the (grouping) results in a report.

You could create a nested query or a view:

Now you can base your report on this query.

or

Note: I did some updates on the original emp-table...