Search code examples
sqlms-accesscasesql-like

CASE WHEN LIKE query SQL

Could you tell me what is wrong with this query?

SELECT Students.Class
FROM Students
GROUP BY Students.class
HAVING SUM(CASE WHEN Name LIKE '*a' THEN 1 ELSE 0 END) > COUNT(*)/2;

I have to check if the name ends with the letter 'a' and then compare if amount of these names in every class is higher than the half of all students in these classes.

Solution

  • MS Access does not support case.

    SELECT Students.Class
FROM Students
GROUP BY Students.class
HAVING SUM(IIF(Name LIKE '*a', 1, 0)) > COUNT(*)/2;