Select count or sum of whole table regardless of join conditions

Question

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?

Answer 1

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 |
+---------+---------+------+------+------+------+