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

Mysql – How do I group by values in multiple columns in SQL?

Imagine a table that looks like this:

| sender_ID | Receiver_ID | message_ID | , say table name is **tb** now I want to write an SQL query that gives me :
user_id, Total messages exchanged by that user . So basically if id = 12 sent 3 messages and received 5 messages, the result will be: 12, 8

I’m not sure if

Select COUNT(MESSAGE_ID)
from tb
group by sender_id, reciever_id

but this is clearly wrong

Tags:

2

Answers


  1. 0

    Use UNION to synthesize a table of all the senders and receivers in one column. Then you can join that with tb to count both IDs.

    WITH users AS (
    SELECT sender_id AS id
    FROM tb
    UNION DISTINCT
    SELECT receiver_id AS id
    FROM tb
)
SELECT u.id, COUNT(*) AS message_count
FROM users u
JOIN tb ON u.id IN (tb.sender_id, tb.receiver_id)
GROUP BY u.id
    Login or Signup to reply.
  2. 0

    This version, also using a CTE to create a single column of users_ids, has just two table scans.

    with cte as (
  select sender_id as user_id, message_id 
  from tb
  union
  select receiver_id, message_id
  from tb
  )
select user_id, count(message_id) as messages
from cte
group by user_id
order by user_id;
    user_id messages
    3601 4
    4500 3
    8752 1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search