SUM(CASE WHEN xxx THEN 1 ELSE 0)

[damccorm]

java.lang.RuntimeException: Aggregate function only accepts Column Reference or CAST(Column Reference) as its input.

I was able to rewrite SQL using WITH statement, and it seemed to work, but requires us rewriting a lot of queries and makes them pretty much unreadable.

Imported from Jira BEAM-8039. Original Jira may contain additional context.
Reported by: amaliujia.