mysql

Select count or sum of whole table regardless of join conditions


I have 2 tables:
POLLS:

POLL_ID CONTENT
1 C1
2 C2
3 C3

VOTES:

POLL_ID USER_ID VOTE
1 U1 1
1 U2 2
2 U1 3
2 U2 2
3 U2 3

I want to query by USER_ID (ex U1) and get result like this:

POLL_ID CONTENT VOTE V1 V2 V3
1 C1 1 1 1 0
2 C2 3 0 1 1

But I get this:

POLL_ID CONTENT VOTE V1 V2 V3
1 C1 1 1 2 2
2 C2 3 1 2 2

V1, V2 and V3 are total count of selected vote (1 to 3) for each POLL_ID, regardless of USER_ID. I want my user to know what he/she voted and what EVERYONE voted for each poll.
My query is like this but it skips the POLL_ID column filter and returns the sum of whole table.

SELECT
    POLLS.POLL_ID,
    POLLS.CONTENT,
    FILTERED_VOTES.VOTE,
    SUM(WHOLE_VOTES.VOTE = 1) AS V1,
    SUM(WHOLE_VOTES.VOTE = 2) AS V2,
    SUM(WHOLE_VOTES.VOTE = 3) AS V3
FROM
    POLLS
JOIN
    VOTES AS FILTERED_VOTES
ON
    POLLS.POLL_ID = FILTERED_VOTES.POLL_ID AND
    FILTERED_VOTES.USER_ID = 1
JOIN
    VOTES AS WHOLE_VOTES
ON
    POLLS.POLL_ID = WHOLE_VOTES.POLL_ID;

Could you please point me where the problem is?


Solution

  • First of all,we can join table polls and table votes,and filter the derived table to include the poll_ids which have user c1 in them. The filtering can be easily done using a subquery in the search condition :

    select *
    from polls p
    join votes v
    using(poll_id)
    where poll_id in (select poll_id from votes where user_id='u1')
    ;
    -- result set
    +---------+---------+---------+------+
    | POLL_ID | CONTENT | USER_ID | VOTE |
    +---------+---------+---------+------+
    |       1 | C1      | U1      |    1 |
    |       1 | C1      | U2      |    2 |
    |       2 | C2      | U1      |    3 |
    |       2 | C2      | U2      |    2 |
    +---------+---------+---------+------+
    

    Then group by the result table from the previous step using poll_id and content, and perform aggregated functions to get the desired report.

    select p.poll_id,p.content, 
    sum(case user_id when 'u1' then vote  end) as vote,
    sum(case v.vote when 1 then 1 else 0 end) as v1,
    sum(case v.vote when 2 then 1 else 0 end) as v2,
    sum(case v.vote when 3 then 1 else 0 end) as v3
    from polls p
    join votes v
    using(poll_id)
    where poll_id in (select poll_id from votes where user_id='u1')
    group by p.poll_id,p.content
    ;
    
    -- result
    +---------+---------+------+------+------+------+
    | poll_id | content | vote | v1   | v2   | v3   |
    +---------+---------+------+------+------+------+
    |       1 | C1      |    1 |    1 |    1 |    0 |
    |       2 | C2      |    3 |    0 |    1 |    1 |
    +---------+---------+------+------+------+------+