CODEWITHSUNDEEP
mysqlsql
Sadat
Sadat

Reputation: 3501

Mysql query: selecting conditional status

I have a table for some voting(yes,no type) data with voter id. the table structure is as below-

`votes(topic_id, voter_id, vote_type, vote_value);`

Vote_value can be 0 or 1 for now. So topic-wise vote frequency is found with the following query

`SELECT topic_id, vote_type, sum(vote_value) vote_frequency 
from votes group by topic_id, vote_type;`

result will be like-

topic_id    vote_type   vote_frequency  
23      like        10  
45      xyz         7  
1023    fav         25

But I want to find the frequency and with the status whether a specific voter voted this topic with current vote_type or not.

EDIT
Result i need like: Let i am finding votes(as above) with the status for voter_id 5, whether he voted or not (1 means voted and 0 means not voted) 

topic_id   vote_type   vote_frequency  this_voter_has_voted  
23          like         10         1  
45          xyz           7         0  
1023        fav          25         1

Note: i have solved using join query, is it possible by avoiding join and sub query?

Upvotes: 1

Views: 118

Answers (1)

Martin Smith
Martin Smith

Reputation: 453287

Yes, You don't need a JOIN or subquery.

SELECT topic_id,
       vote_type,
       SUM(vote_value) vote_frequency,
       MAX(CASE
             WHEN voter_id = 5 THEN 1
             ELSE 0
           END)        AS this_voter_has_voted
FROM   votes
GROUP  BY topic_id,
          vote_type;

Upvotes: 2

Related Questions