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?.