QUESTION POSED ON: 20 August 2003
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 expression
But 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.
|
