Question 271039 in Mysql Question posted in
The official MySQL documentation can be found here.

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?

Tags:

2

Answers


  1. 0

    Should your table description for VOTES have OWNER_ID instead of USER_ID (according to your query)?

    What happens if you move FILTERED_VOTES.OWNER_ID = 1 from the ON clause to a WHERE clause?

    Login or Signup to reply.
  2. 0

    You only need to join both tables once

    and GROUP BY tghe polls table columns,

    it is a simple pivot

    SELECT
  p.POLL_ID, p.CONTENT
  ,SUM(CASE WHEN v.VOTE = 1 THEN 1 ELSe 0 ENd) vote_1
  ,SUM(CASE WHEN v.VOTE = 2 THEN 1 ELSe 0 ENd) vote_2
  ,SUM(CASE WHEN v.VOTE = 3 THEN 1 ELSe 0 ENd) vote_3
FROM polls p JOIN votes v ON p.POLL_ID = v.POLL_ID
GROUP BY p.POLL_ID, p.CONTENT
    POLL_ID CONTENT vote_1 vote_2 vote_3
    1 C1 1 1 0
    2 C2 0 1 1
    3 C3 0 0 1

    fiddle

    and if you only want to know what user U! voted

    SELECT
  p.POLL_ID, p.CONTENT
  ,SUM(CASE WHEN v.VOTE = 1 THEN 1 ELSe 0 ENd) vote_1
  ,SUM(CASE WHEN v.VOTE = 2 THEN 1 ELSe 0 ENd) vote_2
  ,SUM(CASE WHEN v.VOTE = 3 THEN 1 ELSe 0 ENd) vote_3
FROM polls p JOIN votes v ON p.POLL_ID = v.POLL_ID
WHERE v.USER_ID = 'U1'
  GROUP BY p.POLL_ID, p.CONTENT
    POLL_ID CONTENT vote_1 vote_2 vote_3
    1 C1 1 0 0
    2 C2 0 0 1

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search