MYSQL SELECT including COUNT within the same table with reference to specific column

Question

So, I'm getting data from one table with a simple SELECT statement with SELECT COUNT included.

SELECT *, 
    (SELECT COUNT(`message_id`) 
    FROM `user_message` 
    WHERE `status_to` = 0
    ) AS `unread`  
FROM `user_message`

Here, unread counts for unread messages coming from other users. But that's not quite enough. What I want is to reference SELECT COUNT to a specific column, user_id, within the same table:

SELECT *, 
    (SELECT COUNT(`message_id`) 
    FROM `user_message` 
    WHERE `status_to` = 0 AND `user_message`.`user_id` = `user_message`.`user_id`
    ) AS `unread` 
FROM `user_message`

.. if that makes sense. My second statement disregards this part: AND user_message.user_id = user_message.user_id, and gives me the same result for each user_id.

What am I missing??

M Khalid Junaid · Accepted Answer · 2017-10-14 21:32:33Z

1

You need to give different aliases to your table to get the related count

SELECT *, (
    SELECT COUNT(`message_id`) 
    FROM `user_message` b 
    WHERE `status_to` = 0 
    AND `a`.`user_id` = `b`.`user_id`
    ) AS `unread`
FROM `user_message` a

answered Oct 14, 2017 at 21:32

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MYSQL SELECT including COUNT within the same table with reference to specific column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related