Expressions with aggregates

[sanikolaev]

Things like select sum(...) / count(*) etc. are not allowed now:

mysql> drop table if exists t; create table t(a int); insert into t(a) values(1),(2),(1); select sum(if(a,1,0))/count(*) from t;
--------------
drop table if exists t
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
create table t(a int)
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
insert into t(a) values(1),(2),(1)
--------------

Query OK, 3 rows affected (0.00 sec)

--------------
select sum(if(a,1,0))/count(*) from t
--------------

ERROR 1064 (42000): sphinxql: syntax error, unexpected '/' near '/count(*) from t'

The workaround is to decompose the expression:

--------------
select sum(if(a,1,0)), count(*) from t
--------------

+----------------+----------+
| sum(if(a,1,0)) | count(*) |
+----------------+----------+
|              3 |        3 |
+----------------+----------+
1 row in set (0.00 sec)

and calculate the result outside Manticore.

The suggestion is to make it possible to do the calculations right in Manticore.

[barryhunter]

If it just want sum(if(a,1,0))/count(*) specifically, can use avg(if(a,1,0)) - if think about what avg() does internally it's the same thing. Useful trick if wanting order results by a 'percentage'.

 select sum(if(a=1,1,0)),count(*),avg(if(a=1,1,0)) from t;
+------------------+----------+------------------+
| sum(if(a=1,1,0)) | count(*) | avg(if(a=1,1,0)) |
+------------------+----------+------------------+
|                2 |        3 |       0.66666667 |
+------------------+----------+------------------+
1 row in set (0.001 sec)

But yes, agree it would be nice to be able to more general expressions with aggregates.