Counting number of occurrences of a value resulting in “Mismatched Input” error?

So, I have a table where a bunch of people took multiple surveys. Each survey had a rating out of 10. I need to find the average per survey (which I’ve already done) and also the number of 10s that each survey received.

Below is the code I thought would work:

SELECT person, survey, AVG(rating), COUNT(CASE WHEN rating = 10) 
FROM Table
GROUP BY person, survey

The error that I get is: mismatched input ‘WHEN’ expecting {‘)’, ‘,’}

Not sure where I’ve gone wrong. Appreciate any help.

Answer

The CASE expression in your call to COUNT is incomplete. Use this version:

SELECT
    person,
    survey,
    AVG(rating) AS avg_rating,
    COUNT(CASE WHEN rating = 10 THEN 1 END) AS cnt
FROM Table
GROUP BY
    person,
    survey;

The COUNT() will count 1 for every record having a 10 rating, otherwise it will count NULL, which just gets ignored.

Note that on some databases, such as MySQL or Postgres, you may actually sum a direct boolean expression:

SUM(rating = 10)

But the longer version above should work on pretty much any database.