How to put a CASE statement in a GROUP BY clause

Question

I have a single column table, which is a list of possible values, separated by commas. I would like to request, grouped by each individual possible value.

As a test, I wrote this query:

SELECT `Please_identify_which_of_the_following_classroom_hardware_you_c2`, count(`_How_would_you_rate_your_overall_skill_in_using_educational_tec1`) as count, `_How_would_you_rate_your_overall_skill_in_using_educational_tec1` FROM `data_Copy_of_Faculty_survey_on_technology_in_the_classroom_Respo` GROUP BY `_How_would_you_rate_your_overall_skill_in_using_educational_tec1`, CASE WHEN `Please_identify_which_of_the_following_classroom_hardware_you_c2` LIKE '%Elmo%' THEN 'Elmo' END 

(Please excuse column names, they are automatically generated)

I know that the CASE statement is not very useful at the moment, but I'm just trying to run a query. I get an error message:

ERROR 1064 (42000): You have an error in the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax to use next to "THEN" Elmo "END" on line 10

Throughout life, I cannot find what is wrong with the request. Any insight would be appreciated.

EDIT: I tried with single and double quotes - the same problem, regardless of the quote used.

UPDATED: As Mark noted, even if I get this query for parsing, the results will not be what I'm looking for. I'm still wondering why this is not being parsed, but the query is not a solution to my original problem.

Answer 1

The reason you see the problems is because your GROUP BY attributes did not match the SELECT attributes.

How MySql docs puts it :

 "SQL92 and earlier does not permit queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns" 

In other words, since the ...c2 attribute was not "functionally dependent" on your CASE ... END attribute, there was a mismatch between SELECT and GROUP BY and therefore an error.

One way to mitigate the error (and possibly make the request more readable) is to do CASE once, and then execute the aggregates based on the resulting ratio.

 SELECT c2, tec1, COUNT(tec1) FROM (SELECT CASE WHEN `Please_identify_which_of_the_following_classroom_hardware_you_c2` LIKE '%Elmo%' THEN 'Elmo' ELSE `Please_identify_which_of_the_following_classroom_hardware_you_c2` END AS c2, `_How_would_you_rate_your_overall_skill_in_using_educational_tec1`) AS tec1 FROM `data_Copy_of_Faculty_survey_on_technology_in_the_classroom_Respo`) t GROUP BY c2, tec1 

Answer 2

Try the following:

 SELECT CASE WHEN `Please_identify_which_of_the_following_classroom_hardware_you_c2` LIKE '%Elmo%' THEN 'Elmo' ELSE `Please_identify_which_of_the_following_classroom_hardware_you_c2` END AS `Please_identify_which_of_the_following_classroom_hardware_you_c2`, count(`_How_would_you_rate_your_overall_skill_in_using_educational_tec1`) as count, `_How_would_you_rate_your_overall_skill_in_using_educational_tec1` FROM `data_Copy_of_Faculty_survey_on_technology_in_the_classroom_Respo` GROUP BY `_How_would_you_rate_your_overall_skill_in_using_educational_tec1`, CASE WHEN `Please_identify_which_of_the_following_classroom_hardware_you_c2` LIKE '%Elmo%' THEN 'Elmo' ELSE `Please_identify_which_of_the_following_classroom_hardware_you_c2` END