ORA-00937 when calculating a sub-value in an aggregate select statement

Question

The problem described below applies to Oracle 11g databases. I also tested the statements on MySQL and PostgreSQL with no problems of the kind mentioned in this question.

I want to use an aggregate-function in a select-statement which needs some additional calculation to yield the result. My main goal is to keep the whole calculation in one statement since the result is to be displayed verbatim in the backend software.

Let there be a table T defined by this DDL

create table T(
  ID number,
  NUM number,
  TXT varchar(32)
);

insert into T (ID, NUM, TXT) values (1, 1, 'Text1');
insert into T (ID, NUM, TXT) values (2, 2, 'Text2');
insert into T (ID, NUM, TXT) values (3, 0, 'Text3');

Now the objective is to sum all values of the num-attribute for all selected rows. I can do this by using

select sum(num) from T

Now for the tricky part, assume there is a second table from which we calculate an additional value to be combined with the result of our first select. The select involved will return a single number by being an aggregated value by itself. (To keep the schema simple for this demonstration, I use the same table, but the described problem remains regardless of the second table being used)

select case (select count(*) from T) when 0 then 1 else 0 end from dual;

When combined, we will leave out the select ... from dual part which is only used to get the intermediate result displayed. Together the query will be

select (case (select count(t1.num) from T t1) when 0 then 1 else 0 end)*sum(t2.num) from T t2;

where we assign aliases to each table to make the usage clear.

The result of this query is an Oracle exception: ORA-00937: not a single-group group function. Since the case-expression yields a single value and no reference is made to any resources outside the expression, I wonder what basic principle I violated by this select-statement. In addition to that, MySQL and PostgreSQL yield the desired result.

As I was searching for a solution, I also tried this select-statement:

select (case when (exists (select 1 from T where NUM = 0)) then 0 else 1 end)*sum(NUM) as p from T where NUM > 0;

Here I disposed of the inner aggregate-function count(t1.num) to rule out problems with nested aggregations. This statement gave the same Oracle error message, while the calculation of the additional value caused no trouble:

select (case when (exists (select 1 from T where NUM = 0)) then 0 else 1 end) as p from dual;

Question: Can someone please explain what goes wrong? (And especially I wonder if Oracle does not implement standard SQL, or of MySQL and PostgreSQL extend the standard).

Technically the solution to my problem would be a join where both, the aggregate and the additional value are computed separately, and then combined in the outer select-statement. My focus does not lie on how one can solve this problem, but on am I missing something in Oracle, or is Oracle missing something?.

Answer 1

Your additional calculated value:

(select count(t1.num) from T t1)

Is a scalar subquery, which is a dynamic rather than static expression. As such it's treated the same as a column as far as the aggregate is concerned and needs to be included in the group by clause to avoid the ORA-00937: not a single-group group function error

However, oracle does not allow subqueries as part of the group by clause and trying to include the scalar subquery and/or the whole case statement:

group by (case (select count(*) cnt from t t1) when 0 then 1 else 0 end)

just results in an ORA-22818: subquery expressions not allowed here error.

The only ways around this are to either convert your scalar subquery to an aggregate value like so:

max(case (select count(*) cnt from t t1) when 0 then 1 else 0 end)

or

(case max((select count(*) cnt from t t1)) when 0 then 1 else 0 end)

or rewrite your query to move the unaggregated scalar subquery out of the aggregated query:

select (case (select count(*) cnt from t t1) when 0 then 1 else 0 end) * sum
  from (select sum(t3.num) sum from t t3) t2;

or precompute your scalar subquery so it can be used in the group by clause:

select case t1.cnt when 0 then 1 else 0 end * sum(t2.num)
  from t t2
     , (select count(*) cnt from t) t1
 group by case t1.cnt when 0 then 1 else 0 end