SERPland Oracle, PL/SQL, PHP, Autos, Bungalow-Hotels and more …

Oracle SQL: Summarize a calculated column (like a count(*) column) with ROLLUP

You have a simple sql statement like this:

select machine, count(*) as users
from v$session
group by machine

This query returns the session count on each machine:

MACHINE USERS
machine1 16
machine2 17
machine3 15
machine4 14

Now you'd like to know the total number of users. You could manually summarize 16, 17, 15 and 14 to the total of 62.
Or you could execute another query without grouping the "machine".
The best way would be an automated summary of the number of users in our actual query.
It's simple!

Just use the ROLLUP analytical function:

select machine, count(*) as users
from v$session
group by rollup (machine)

This ease use of the rollup analytical functions summarizes our users count.
Now, an additional row in the query result set return the total users count of 62.

MACHINE USERS
machine1 16
machine2 17
machine3 15
machine4 14
"total" . . . 62

Reference:

SELECT [column,] group function(column) . ,
GROUPING(expr)
FROM table
[WHERE condition]
[GROUP BY [ROLLUP][CUBE] group by expression]
[HAVING having expression ]
[ORDER BY column];

When using mySql instead of Oracle, just use the "WITH ROLLUP" clause.

Bookmark It