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

aggregateMySQLoraclepostgresql

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

Best Answer

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)

(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

Related Solutions

Postgresql – Postgres Nested WHEN Aggregate Function

After some processing this boiled down to:

While your predicate d."SettlementPointName" = 'John' is filtering a single value for "SettlementPointName" anyway, simplify to:

SELECT count(                                     d."SettlementPointPrice" < 10.5 OR NULL) AS da_00_10
     , count(d."SettlementPointPrice" >= 10.5 AND d."SettlementPointPrice" < 20.5 OR NULL) AS da_11_20
     , count(d."SettlementPointPrice" >= 20.5 AND d."SettlementPointPrice" < 30.5 OR NULL) AS da_21_30
FROM   public.da d
JOIN   public.rt_aggregate r USING ("DeliveryDate", "SettlementPointName")
WHERE  d."SettlementPointName" = 'John'
AND    d."DeliveryDate" >= '2015-02-01'
AND    d."DeliveryDate" <= '2015-02-20'
AND    r."DeliveryHour" = 14
AND    date_part('hour', d."DeliveryHour") = r."DeliveryHour";

About the counting technique:

For absolute performance, is SUM faster or COUNT?

Or better, yet, use the new aggregate filter technique in pg 9.4:

SELECT d."SettlementPointName"
     , count(*) FILTER (WHERE d."SettlementPointPrice" <  10.5) AS da_00_10
     , count(*) FILTER (WHERE d."SettlementPointPrice" >= 10.5
                        AND   d."SettlementPointPrice" <  20.5) AS da_11_20
     , count(*) FILTER (WHERE d."SettlementPointPrice" >= 20.5
                        AND   d."SettlementPointPrice" <  30.5) AS da_21_30
FROM   public.da d
JOIN   public.rt_aggregate r USING ("DeliveryDate", "SettlementPointName")
WHERE  d."DeliveryDate" >= '2015-02-01'
AND    d."DeliveryDate" <= '2015-02-20'
AND    r."DeliveryHour" = 14
AND    date_part('hour', d."DeliveryHour") = r."DeliveryHour"
GROUP  BY 1;

This time, selecting all names and returning one row per name like you asked in the comment.

Details for FILTER:

Return counts for multiple ranges in a single SELECT statement

Mysql – Logic Value of Assignment Expression

PROPOSED SOLUTION

Using the IF() function to check for consecutive num values

SET @x = 0;
SET @n = 0;
SELECT num,consecutive FROM
(SELECT
    id,num,
    (@x:=IF(num=@n,@x+1,1)) consecutive,
    (@n:=num) inc
FROM logs) A
WHERE consecutive >= 3;

YOUR SAMPLE DATA

DROP DATABASE IF EXISTS tonychen;
CREATE DATABASE tonychen;
USE tonychen
CREATE TABLE logs
(
  id INT NOT NULL AUTO_INCREMENT,
  num INT NOT NULL,
  PRIMARY KEY (id)
);
INSERT INTO logs (num) VALUES (1),(1),(1),(2),(1),(2),(2);
SELECT * FROM logs;

YOUR SAMPLE DATA LOADED

mysql> DROP DATABASE IF EXISTS tonychen;
Query OK, 1 row affected (0.25 sec)

mysql> CREATE DATABASE tonychen;
Query OK, 1 row affected (0.00 sec)

mysql> USE tonychen
Database changed
mysql> CREATE TABLE logs
    -> (
    ->   id INT NOT NULL AUTO_INCREMENT,
    ->   num INT NOT NULL,
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.31 sec)

mysql> INSERT INTO logs (num) VALUES (1),(1),(1),(2),(1),(2),(2);
Query OK, 7 rows affected (0.05 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM logs;
+----+-----+
| id | num |
+----+-----+
|  1 |   1 |
|  2 |   1 |
|  3 |   1 |
|  4 |   2 |
|  5 |   1 |
|  6 |   2 |
|  7 |   2 |
+----+-----+
7 rows in set (0.00 sec)

mysql>

PROPOSED SOLUTION EXECUTED

mysql> SET @x = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @n = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT num,consecutive FROM
    -> (SELECT
    ->     id,num,
    ->     (@x:=IF(num=@n,@x+1,1)) consecutive,
    ->     (@n:=num) inc
    -> FROM logs) A
    -> WHERE consecutive >= 3;
+-----+-------------+
| num | consecutive |
+-----+-------------+
|   1 |           3 |
+-----+-------------+
1 row in set (0.00 sec)

mysql>