How to put a CASE statement in the GROUP BY clause

Question

I've got a table with one column that is a comma separated list of possible values. I'd like to query, grouped by each individual possible value.

As a test, I've written 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 the column names, they're auto-generated)

I know the CASE statement isn't incredibly useful at this point, but I'm just trying to get the query to run. I'm getting an error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'THEN 'Elmo' END' at line 10

For the life of me I can't find what's wrong with the query. Any insight would be appreciated.

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

UPDATED: As Mark has pointed out, even if I get this query to parse, the results won't be what I'm looking for. I'm still curious why this doesn't parse, but the query is not the solution to my initial problem.

Answer 1

The reason you're seeing issues is that your GROUP BY attributes didn't align with the SELECT attributes.

As the MySql docs put 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 thus an error.

One way to mitigate the error (and possibly make the query more readable), is to do the CASE once and then do the aggregates on the resulting relation.

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 This:

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