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

How to count number of user chat with another user – Mysql

I try few mysql statement but didn’t come to my expectations.

enter image description here

How to get the total of to_user chat and order by the lowest total?

Let say in this case,

id 7 chat with 2 user

id 6 chat with only 1 user.

so the minimum will be id 6.

Can someone help me with sql statement?

This is what my expected result

count to_user
1 7
2 6
Tags:

2

Answers


  1. 0

    I think your problem will be solved with the following code:

    SELECT COUNT(DISTINCT(from_user)) AS total,to_user
FROM chats
GROUP BY to_user
ORDER BY total ASC
    Login or Signup to reply.
  2. 0

    Make sure you index the junction in both directions, otherwise it will BYITA later!

    ALTER TABLE messages
    ADD INDEX idx_from_to (from_user, to_user),
    ADD INDEX idx_to_from (to_user, from_user);

    You may want to take into account the fact that the full list of chats for a given user is (from any user, to given user) UNION (to any user, from given user).

    Consider user 1 in your sample data, who has sent messages to 5, 6, & 7 but not received any. And user 5 has sent to user 7 and received from user 1.

    SELECT COUNT(DISTINCT(from_user)) AS count, to_user
FROM messages
GROUP BY to_user
ORDER BY count ASC

    returns the following (which matches the expected result detailed in your question, errors aside)

    count to_user
    1 5
    1 6
    2 7

    whereas

    SELECT COUNT(*) AS count, from_user AS user
FROM (
    SELECT from_user, to_user FROM messages
    UNION
    SELECT to_user, from_user FROM messages
) t
GROUP BY from_user
ORDER BY count ASC;

    returns

    count user
    1 6
    2 5
    2 7
    3 1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search