CODEWITHSUNDEEP
mysqlsqlgroup-by
Thomas Clayson
Thomas Clayson

Reputation: 29925

SUM() all results (no group by clause)

I'm having a strange error. I have SQL code which looks like:

SELECT 
    adverse_reaction_type_id,
    SUM(CASE adverse_reaction_type_id WHEN 1 THEN `number` ELSE 0 END ) line, 
    SUM(CASE adverse_reaction_type_id WHEN 2 THEN `number` ELSE 0 END ) drug 
FROM
    core_reports_adverse_reactions, 
    ...
WHERE 
    ...

This works fine on my MAMP installation, but doesn't on my remote server. I assume there's a config option somewhere which is stopping it from working.

This is the error I'm getting:

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

I know what the error is telling me, but the problem is that I don't want to group by anything. I want to get all results where the where bit (removed above for brevity) matches and then return the SUM() of the number column (as either line or drug based on the CASE clause).

So essentially, regardless of what is matched by the WHERE clause all I want to return is 1 row:

line | drug
-----------
10   | 32

Is there a way around this at all? Or any way I can GROUP BY nothing or something?

Upvotes: 10

Views: 8529

Answers (1)

LSerni
LSerni

Reputation: 57408

You can GROUP BY a constant on the table where you run the SELECT.

For example GROUP BY NULL:

mysql> select SUM(CASE WHEN product_id = 0 THEN -1 ELSE product_id END) AS sumprod, SUM(quantity) AS sumquant FROM orders GROUP BY NULL;
+---------+----------+
| sumprod | sumquant |
+---------+----------+
|       4 |        8 |
+---------+----------+
1 row in set (0.00 sec)

mysql> select version();
+-------------+
| version()   |
+-------------+
| 5.5.25a-log |
+-------------+
1 row in set (0.00 sec)

But usually this error means that there is at least one column which is not grouped.

It might be that the production version of MySQL is taken aback by your use of a CASE within the aggregate function.

Upvotes: 9

Related Questions