You could do something like this. I don’t have your input data, so I used
Notice a few things. I grouped by
JOB, and I used
GROUPING(JOB) both in
SELECT (to add the label
TOTAL for the summary row) and in
ORDER BY. Since I reuse the column name
SELECT (for the output column), in
ORDER BY I must be careful to qualify the column name
JOB (to make it clear I am referring to the input table column, not to the column in
SELECT – which would be the default if column names in
ORDER BY were not qualified). The need to qualify column names in
ORDER BY, then, forced me to alias the table in the
FROM clause (otherwise I would have had to carry the full table name everywhere).
GROUPING function in
SELECT (rather than
NVL) is particularly important if
JOB can be
null. You don’t want the group for
null job to be labeled
TOTAL – you only want that for the rollup row. This point confuses even a lot of very advanced programmers.
I show how you can “manually” decide the order:
PRESIDENT first, then
MANAGER, and then all other jobs (ordered alphabetically). If you have the order of priority saved somewhere, for example in a table, you can join to that table and use the ordering column instead of the “manual”
CASE expression in my query.
select case grouping(job) when 0 then job else 'TOTAL' end as job , sum(sal) as total_salary from scott.emp e group by rollup(job) order by grouping(e.job) -- to get the total in the last row , case e.job when 'PRESIDENT' then 1 when 'MANAGER' then 2 end , e.job ; JOB TOTAL_SALARY --------- ------------ PRESIDENT 5000 MANAGER 8275 ANALYST 6000 CLERK 4150 SALESMAN 5600 TOTAL 29025
CLICK HERE to find out more related problems solutions.