SQL CASE in GROUP BY

user_8466696 0
Viewed 44

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:

enter image description here

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;
conditional-aggregation case exists group-by postgresql
2 Answers

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;
user_104988280

the database interprets reaction as belonging to the alias i.

you need to use the original name (or in my case the alias c) to get te rigt column

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 = c.reaction
                ) then 1
                else 0
            end as "includesMe" 
        FROM "CommentReaction" as c WHERE "commentId" = 'c142787b-4422-4128-8357-58d36c177307' GROUP BY reaction;
user_51935360
Related