Solution for SQL Query to Count Rows when Two Fields Match
is Given Below:
I’m trying to see if it’s possible to count rows when two fields are matching in multiple rows. I have a chat system that stores message history in a sql database. I want to see how many messages were sent from User 1 to User 2 in a certain time frame (monthly). So here’s my sample table of fields:
Sending User | Receiving User | Message Text | Time Stamp User 1 | User 2 | Hi | 08/01/2021 User 3 | User 4 | Hello | 08/01/2021 User 2 | User 1 | Hi Back | 08/01/2021 User 1 | User 2 | Bye | 08/02/2021 User 3 | User 4 | Goodbye | 08/02/2021 User 1 | User 2 | Hello Again | 08/03/2021
So I’m looking for output like this:
Sending User | Receiving User | Message Count User 1 | User 2 | 3 User 2 | User 1 | 1 User 3 | User 4 | 2
I haven’t been able to get the desired result with a Count query with Group By statements yet.
SELECT Sending User, Receiving User, COUNT(Message Text) FROM TABLE GROUP BY Sending User, Receiving User
But so far that’s giving me multiple rows for each user, so I have several rows for the same sending and receiving user combination. I’m expecting just one.
Your desired results don’t take into account any particular date range, but your example query gives exactly your desired results:
select sendinguser, ReceivingUser, Count(*) MessageCount from t where timestamp between '20210801' and '20210831' group by sendinguser,ReceivingUser order by sendinguser