SQL Query to Count Rows when Two Fields Match

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.

I’ve tried:

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

See example DB Fiddle