CODEWITHSUNDEEP
mysqlsql
Martin AJ
Martin AJ

Reputation: 6697

How can I use multiple columns for DISTINCT?

I'm making a messaging system. Messages are two different kind.

Here is my table structure:

// messages
+----+----------+------------------+-----------+-------------+-------------+---------+
| id |  title   |     content      | sender_id | receiver_id |  date_time  | related |
+----+----------+------------------+-----------+-------------+-------------+---------+
| 1  | titel1   | whatever1        | 1         | 3           | 1521097240  | NULL    |
| 2  |          | whatever2        | 3         | 1           | 1521097241  | 1       |
| 3  |          | whatever3        | 1         | 3           | 1521097242  | 1       |
| 4  | title2   | whatever4        | 1         | 4           | 1521097243  | NULL    |
| 5  | title3   | whatever5        | 1         | 5           | 1521097244  | NULL    |
| 6  |          | whatever6        | 5         | 1           | 1521097245  | 5       |
| 7  |          | whatever7        | 4         | 1           | 1521097246  | 4       |
| 8  | title4   | whatever8        | 1         | 4           | 1521097246  | NULL    |
+----+----------+------------------+-----------+-------------+-------------+---------+
/*
  related column: it is NULL for the first message and the id of the parent for othesrs.

Now I need to count the number of messages an user sent in a day to different users and different new messages.

So the expected result is 4 for sender_id = 1. Here is my current query which returns 3:

SELECT count(distinct receiver_id) as sent_messages_num
FROM users
WHERE sender_id = 1  
AND date_time > UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 1 DAY))

My current query doesn't care about "new messages". It groups messages only per different users. How can I also add the concept of "new messages should be count too even if they are sent to the same user" ?

Upvotes: 1

Views: 75

Answers (1)

Radim Bača
Radim Bača

Reputation: 10701

Try the following query

SELECT sum(sent_messages_num)
FROM
(
    SELECT count(distinct receiver_id) as sent_messages_num
    FROM users
    WHERE sender_id = 1  
    AND date_time > UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 1 DAY))
    GROUP BY CASE WHEN related IS NULL THEN id ELSE related END
) t

dbfiddle demo

Upvotes: 2

Related Questions