Related to your answer to the question on counts and percentages, I checked the problem out on ORACLE 8.1.6.00 (Oracle 8i) and found the following.
This query returns an error:
SELECT deptno, -- Query1 SUM (sal), ( SUM (sal) * 100 ) / (SELECT SUM (sal) FROM emp) AS pct FROM emp GROUP BY deptno; ERROR at line 1: ORA-00979: not a GROUP BY expressionBut the following query works:
SELECT deptno, -- Query2 SUM (sal), ( SUM (sal) * 100 ) / MAX ((SELECT SUM (sal) FROM emp )) AS PCT FROM emp GROUP BY deptno; DEPTNO SUM(SAL) PCT ---------- ---------- ---------- 10 8750 30.1464255 20 10875 37.4677003 30 9400 32.3858742 3 rows selected.In my opinion, Query1 should have worked also because the "scalar" select should be interpreted as a "constant" with respect to the surrounding SELECT. And it is really interpreted as such in the case that the outer SELECT doesn't have a "GROUP BY". But if it has, then the "scalar" SELECT requires a group function also, so I added the "MAX" function, which in fact does nothing to the SUM returned by the "scalar" select.
I agree: it's somewhat inconsistent for Query1 not to work when Query2 does. I can't find anything in Oracle 8 documentation suggesting that either query would work. However, in version 9.2 of Oracle, they both work, and the documentation confirms that the number of places where scalar subquery expressions (subqueries that return no more than one row and exactly one column) has been enlarged to include most places where you can use more mundane expressions. There have been other cases where features first announced in one version of Oracle were partially available (but undocumented) in earlier versions. My earlier response started off wrong: I should have said, "Unfortunately, in version 8, you can't always use a subquery ...".
There's no reason not to use Query2 in version 8: it works now and it will be supported in the future. But don't be too surprised if seemingly similar things (like Query1) aren't available yet.
This was first published in August 2003