More on counts and percentages

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.

    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top