SQL CASE in GROUP BY

June 26, 2022

I have a query that is meant to get the count of each reaction on a comment and whether the user is one of the people who made that reaction. However, my case statement returns includesMe:1 for EVERY reaction when the user has made at least one reaction on the comment.

Eg, if the user only reacted to 🥰, my query returns that they reacted to every reaction:

SELECT count(*) as "numReacts", 
       reaction, 
       case when exists(
            select *
            from "CommentReaction" as i
            where i."userId" = 'b8b660c9-c416-42b6-9142-19112a9ff811' 
              and i."commentId" = 'c142787b-4422-4128-8357-58d36c177307' 
              and i.reaction = reaction
            ) 
            then 1
            else 0
       end as "includesMe" 
FROM "CommentReaction" 
WHERE "commentId" = 'c142787b-4422-4128-8357-58d36c177307' 
GROUP BY reaction;

>Solution :

The condition:

and i.reaction = reaction

is evaluated as TRUE because the unqualified column reaction is considered to be the column of the inner subquery’s table.
You could alias the outer table, for example

FROM "CommentReaction" AS t

and change to

and i.reaction = t.reaction

but I believe that you can solve your problem with a simpler query if you use conditional aggregation:

SELECT COUNT(*) AS "numReacts", 
       reaction, 
       MAX(CASE WHEN "userId" = 'b8b660c9-c416-42b6-9142-19112a9ff811' THEN 1 ELSE 0 END) AS "includesMe" 
FROM "CommentReaction" 
WHERE "commentId" = 'c142787b-4422-4128-8357-58d36c177307' 
GROUP BY reaction;

or:

SELECT COUNT(*) AS "numReacts", 
       reaction, 
       MAX(("userId" = 'b8b660c9-c416-42b6-9142-19112a9ff811')::int) AS "includesMe" 
FROM "CommentReaction" 
WHERE "commentId" = 'c142787b-4422-4128-8357-58d36c177307' 
GROUP BY reaction;