MYSQL Query - Unexpected result in text messaging database On a text messaging database I have built, I am trying to return the total amount of messages a user has sent and the total number of messages a user has received. I have the following code:

SELECT People.Name,
SUM(Case When Recipients.RecipientID = People.ID THEN 1 ELSE NULL END) AS 'Messages Received',
Count(Messages.SenderID = People.ID) AS 'Messages Sent'
FROM People JOIN Recipients, Messages
GROUP BY People.ID
ORDER BY People.ID ASC;


which returns unexpected results. I am just using a test database of 3 messages sent. 2 by 1 person and 1 by another person. All 3 messages were group messages sent to the other 9 people listed in the database. So I would expect one user to have 2 messages sent and 1 received, one of the users to have 1 message sent and 2 received, and the other 8 people to have 0 sent and 3 received.

What I am actually getting is the two users who have sent message(s) have 3 messages received and 54 messages sent. The other users all have 6 messages received and 54 messages sent. 

The tables are set up as follows:

Messages (MessageID int, SenderID int, Content varchar, Type varchar, DateTime datetime)
People (ID int, Name varchar)
Recipients (RecipientID int, MessageID int)


Because each message was sent to 9 people, there are 9 entries for each messageID in the Recipients table, with each one responding to a recipientID (which corresponds to People.ID).

Where does my query go wrong?

Also, in a somewhat related question, what would be the best way to modify the database if I want to add Groups and then just add people ID's to groups and allow a group to be a recipient and each person that is a member of that specific group gets counted as a recipient?
Your SELECT clauses may not be optimal. I suspect that Count(Messages.SenderID = People.ID) equals the total number of rows in the result set: for each row, Messages.SenderID = People.ID equals either 1 or 0, and MySQL simply counts all the 1s and 0s, and it ends up with a total number of 54 (6 times 9). Try using SUM(Messages.SenderID = People.ID) instead.

By the same logic, I would replace SUM(Case When Recipients.RecipientID = People.ID THEN 1 ELSE NULL END) with SUM(Recipients.RecipientID = People.ID).