Try the ROLLUP analytical function. ROLLUP enables an SQL statement to calculate multiple levels of subtotals across a specified group of dimensions. It also calculates a grand total. ROLLUP is a simple extension to the GROUP BY clause, so its syntax is extremely easy to use. It also only does one-pass through the table, so it's efficient too!
SELECT deptno, job, count(*), sum(sal) FROM emp GROUP BY ROLLUP(deptno,job); DEPTNO JOB COUNT(*) SUM(SAL) --------- --------- --------- --------- 10 CLERK 1 1300 10 MANAGER 1 2450 10 PRESIDENT 1 5000 10 3 8750 20 ANALYST 2 6000 20 CLERK 2 1900 20 MANAGER 1 2975 20 5 10875
Dig Deeper on Oracle and SQL
Related Q&A from Don Burleson
Suppose you have a table which has only two columns, namely locid (not primary) and place. In Oracle, which query should I run to get the output as ... Continue Reading
I have created a table, Party, and in it there are two main fields, party_code and party_name, and around 2,500 records are available in it. I passed... Continue Reading
I have a query that retrieves around 10 records. I have a task to concatenate the values separated with ','. I would like to know if Oracle has any ... Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.