karpengold karpengold - 6 months ago 31
MySQL Question

search by date mysql

I have a table named

messages
:

date (format DATETIME)
id
text
user_id(FK)


and a table named
users
:

id
name


how to get count of messages with given date (
message date = xxxx-xx-xx
) for each user?
like:

user message_count
user1_name 5
user2_name 2


and how get users with 3 or more message today?

SELECT users.name
FROM users
WHERE Users.id IN ( SELECT Messages.user_id
FROM chat.Messages
GROUP BY Messages.user_id
HAVING COUNT(*) >=3
)


It returns users who have 3 or more messages, how to get users, who have 3 or more message today?
where date = now()

Answer

How to get count of messages with given date (message date = xxxx-xx-xx) for each user?

Query #1:

SELECT 
users.name,
COUNT(*) message_count
FROM messages 
INNER JOIN users ON messages.user_id = users.id
WHERE messages.date = PUT_YOUR_DATE_HERE
GROUP BY messages.user_id;

How to get count of messages for those users who have 3 or more messages today?

Query #2:

SELECT 
users.name,
COUNT(*) message_count
FROM messages 
INNER JOIN users ON messages.user_id = users.id
WHERE messages.date = CURDATE()
GROUP BY messages.user_id
HAVING COUNT(*) >= 3;