MYSQL case statement with sum query

Question

Not sure if this is possible - but I am trying to do the following Have a table with following colums

 vote_id (primary_key)
 question_id
 user_id
 vote_count

This basically stores all the votes casted by users for a particular question

Now in a single query is it possible for me to get the total vote count and check if a particular user has casted his vote or not.

Something along these lines - lets say for user_id 10

SELECT sum(vote),
    (
        CASE 
            WHEN (user_id = 10)
                THEN user_id
            ELSE NULL
            END
        ) user_id
FROM vote_question
GROUP BY course_question_id

This obviously doesn't work.

What I'm expecting is if a particular user has voted - his user_id should be returned along with vote count - if he not voted - return null

Thanks

If the answer below isn't what you're after (it seems like it might be), consider providing proper DDLs and/or an sqlfiddle TOGETHER WITH THE DESIRED RESULT — Strawberry
– Strawberry, Commented Oct 28, 2013 at 9:47

Alma Do · Accepted Answer · 2013-10-28 09:45:29Z

1

That will be:

SELECT 
  SUM(vote),
  COUNT(IF(user_id = 10, 1, NULL)) AS has_user_id_10,
FROM 
  vote_question
GROUP BY 
  course_question_id

-this will result with has_user_id_10 greater than zero if user with id 10 cast his vote, and zero - if not. May be you want strict 1 or 0, then simply replace that with

COUNT(IF(user_id = 10, 1, NULL))>0 AS has_user_id_10

answered Oct 28, 2013 at 9:45

Alma Do

37.4k10 gold badges81 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aditya Kakirde · Accepted Answer · 2013-10-28 09:47:55Z

0

Please try this -

select user_id, vote_count
from
(
select sum(vote) vote_count ,user_id, course_question_id from vote_question group by user_id, course_question_id
)
where vote_count <>0;

answered Oct 28, 2013 at 9:47

Aditya Kakirde

5,3051 gold badge15 silver badges10 bronze badges

Collectives™ on Stack Overflow

MYSQL case statement with sum query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related