MySQL – Which MultiStatements are Out of Sync

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

I don't think the problem is with your query, but rather with your data. Check out the error message

It ssays : #2014 - Commands out of sync; you can't run this command now and 1690 BIGINT UNSIGNED value is out of range in '(bt.end_val-bt.start_val)'

I think for some reason, now (bad data entry?), your query is trying to insert a negative value into an UNSIGNED INT which is not allowed.

[EDIT]

I found it strange that you couldn't subract one integer from another so I tested with a sample data set.

mysql> CREATE TABLE `test1` (
    ->   `start_val` int(10) unsigned NOT NULL,
    ->   `end_val` int(10) unsigned NOT NULL
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.50 sec)

mysql> insert into test1 values(3, 4);
Query OK, 1 row affected (0.05 sec)

mysql> insert into test1 values(4, 3);
Query OK, 1 row affected (0.05 sec)

So, two simple records in a simple table.

Then I ran this very simple query:

mysql> select end_val - start_val from test1;

and INCREDIBLY I got the result:

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`test1`.`end_val` - `test`.`test1`.`start_val`)

I investigated and found that there is a setting

NO_UNSIGNED_SUBTRACTION

By default, subtraction between integer operands produces an UNSIGNED result if any operand isUNSIGNED.

This has to be switched off if you with to subtract UNSIGNED INT values. I consider this to be a bug in MySQL - all the more egregious since there are no CHECK constraints in MySQL - but one could have plenty of reasons to wish to subtract two positve integers and obtain a negative result (debts, whatnot), but you can't do that in MySQL. Absolutely incredible! Unsigned INTs should not be used for anything but PRIMARY KEYs.