If you wish to find, for example, the number of employees by department, you can use the following query (using the standard demo tables in Oracle).
SELECT deptno, count(*) FROM emp GROUP BY deptno;To include the total number of employees, you need to use the UNION clause as follows:
SELECT deptno, count(*) FROM emp GROUP BY deptno UNION SELECT to_number(null), count(*) FROM emp;In Oracle8i, you can use the ROLLUP option to achieve the same result:
SELECT deptno, count(*) FROM emp GROUP BY rollup(deptno);By simply invoking the ROLLUP facility in the GROUP BY clause, we direct Oracle to summarize the data at levels above the columns specified, all the way to the grand total.
Note that Oracle reports the grand total by leaving the GROUP BY column blank. If the GROUP BY column also contains null values, it might be difficult to distinguish those values from the grand total row. Fortunately, you can use a special function called GROUPING to report the current summarization level. The function returns two values: 0 indicates that the current row is grouped at the specified GROUP BY level, and 1 indicates that the row is grouped at a higher level.
SELECT deptno, count(*), grouping(deptno) FROM emp GROUP BY rollup(deptno); DEPTNO COUNT(*) GROUPING(DEPTNO) ---- ---- ---- 10 3 0 20 5 0 30 6 0 14 1
For More Information
- What do you think about this tip? E-mail the editor at email@example.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.