Hi!

Andreas> When using CASE and GROUP BY together in a query and you use the CASE
value Andreas> WHEN [compare-value] syntax instead of CASE WHEN [condition] THEN
result Andreas> syntax you will get an invalid response.

Andreas> Create data for repeating: Andreas> ==========================

Andreas> create table test (t_time int);

Andreas> insert into test values (1); Andreas> insert into test values (2); Andreas> insert into test values (3); Andreas> insert into test values (2); Andreas> insert into test values (1);

Andreas> This query works ok: Andreas> ==================== Andreas> select t_time as wd, Andreas> CASE WHEN t_time = 1 THEN '1' Andreas> WHEN t_time = 2 THEN '2' Andreas> ELSE '0' END Andreas> as data Andreas> from test GROUP BY wd;

Andreas> Whis query DOESN'T work: Andreas> ======================== Andreas> select t_time as wd, Andreas> CASE t_time Andreas> WHEN 1 THEN '1' Andreas> WHEN 2 THEN '2' Andreas> ELSE '0' END Andreas> as data Andreas> from test GROUP BY wd;

What MySQL version and system do you use and what result did you get?

For me the above worked without any problems:

mysql> select t_time as wd, -> CASE t_time -> WHEN 1 THEN '1' -> WHEN 2 THEN '2' -> ELSE '0' END -> as data -> from test GROUP BY wd; +------+------+ | wd | data | +------+------+ | 1 | 1 | | 2 | 2 | | 3 | 0 | +------+------+ 3 rows in set (0.01 sec)

which looks ok for me.

(Note that I am using 3.23.38, so it may be that we have fixed something since your version that fixes this...)

Regards, Monty