CODEWITHSUNDEEP
mysqlsqlcase
Koorosh
Koorosh

Reputation: 301

CASE will ignore duplicate entries

I have a table which store user votes, something like this:

+----+---------+-------+---------+
| id | post_id | value | user_id |
+----+---------+-------+---------+
| 1  |   103   |   1   |    1    |
| 2  |   105   |   1   |    3    |
| 3  |   106   |   1   |    1    |
| 4  |   108   |   0   |    1    |
| 5  |   108   |   0   |    2    |
| 6  |   105   |   0   |    2    |
| 7  |   105   |   1   |    1    |
+----+---------+-------+---------+

Where id is Vote ID, post_id is Post ID, value is a boolean for like/unlike (1 = Like, 0 = Unlike) and user_id is Voter ID.

I want to get a list which has:

  1. All posts
  2. Each post's like count
  3. Each post's unlike count
  4. Vote ID of where a user (for example, user 1) voted. So I can update the value later
  5. The vote that user gave

So I wrote this query:

SELECT
    post_id, 
    COUNT(
        CASE 
            WHEN value = 1 
            THEN 1 
        END
    ) AS likes, 
    COUNT(
        CASE 
            WHEN value = 0 
            THEN 1 
        END
    ) AS unlikes, 
    (CASE 
        WHEN user_id = 1    -- I choose user_id manually
        THEN id 
    END) AS vote_id, 
    (CASE 
        WHEN user_id = 1 
        THEN value 
    END) AS user_vote 
FROM votes 
GROUP BY post_id;

What I expected was something like this:

+---------+-------+---------+---------+-----------+
| post_id | likes | unlikes | vote_id | user_vote |
+---------+-------+---------+---------+-----------+
|   103   |   1   |    0    |    1    |     1     |
|   105   |   2   |    1    |    7    |     1     |
|   106   |   1   |    0    |    3    |     1     |
|   108   |   0   |    2    |    4    |     0     |
+---------+-------+---------+---------+-----------+

But this is the result I have:

+---------+-------+---------+---------+-----------+
| post_id | likes | unlikes | vote_id | user_vote |
+---------+-------+---------+---------+-----------+
|   103   |   1   |    0    |   1     |    1      |
|   105   |   2   |    1    |   NULL  |    NULL   |
|   106   |   1   |    0    |   3     |    1      |
|   108   |   0   |    2    |   4     |    0      |
+---------+-------+---------+---------+-----------+

Those NULL values happen because of the last line of my query (GROUP BY post_id). If there's a duplicate post_id in table (for example, if some other user already voted for that post), CASE will ignore it and return NULL.

What should I do?

Upvotes: 0

Views: 24

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

The problem with your query is the lack of aggregations on the last two columns. Assuming that a user can only vote once for a post, you are safe combining the values using MAX() (or MIN()) because there is at most one matching row:

SELECT v.post_id, SUM(value = 1) AS likes, SUM(value = 0) AS unlikes,
       MAX(CASE WHEN user_id = 1 THEN vote_id END) AS user1_voteid,
       MAX(CASE WHEN user_id = 1 THEN value END) AS user1_vote
FROM votes v
GROUP BY v.post_id;

Upvotes: 3

Related Questions