CODEWITHSUNDEEP
phpmysql
Jordy
Jordy

Reputation: 4809

How to find total of records?

how can I find the total records in this case:

I have a table 'users', a table 'messages' and a table 'groups'. A user can sent a message, and that will be stored in the table 'messages'. Each user can be a member of one group. Now I would like to show the total messages sent by all users in the group on the group-details-page. How can I get all the users in the group and count their messages? What is the best and fastest way to do that?

I can't work with Joins, so this doesn't work. I don't know how to fix it.

SELECT COUNT(Message_id) AS total_messages, d.Userid FROM messages AS d LEFT JOIN users AS s ON (s.Groupid=$groupid) WHERE s.Groupid=$groupid

Thanks!

If you need table structure:

** users **
Userid
Groupid

** groups **
Groupid
Some_details

** messages **
Messageid
Userid
Subject
Content

Upvotes: 0

Views: 101

Answers (3)

Ganesh Bora
Ganesh Bora

Reputation: 1153

Select  Groupid,Some_details,count(Messageid) as count_message from groups Left join users On 
users.Groupid = groups.Groupid left join messages on messages.Userid = users.users.Userid GROUP BY Groupid

Upvotes: 0

Uttara
Uttara

Reputation: 2534

If you want all users in a particular group with their total messages then try out this

SELECT messages.Userid, COUNT(messages.Messageid) as total_messages
FROM messages
    INNER JOIN users ON messages.Userid = users.Userid
WHERE users.Groupid = $groupid GROUPBY messages.Userid

Upvotes: 0

Aaron W.
Aaron W.

Reputation: 9299

If you're looking for total messages without needing to know per user, try

SELECT COUNT(messages.Messageid) as total_messages
FROM messages
    INNER JOIN users ON messages.Userid = users.Userid
WHERE users.Groupid = $groupid

Make sure you're indexing the messages.Userid field as well if you'll be doing a lot of querying based on it.

Needing a count per user try

SELECT 
    COUNT(messages.Messageid) as num_messages,
    messages.Userid
FROM messages
    INNER JOIN users ON messages.Userid = users.Userid
WHERE users.Groupid = $groupid
GROUP BY users.Userid

Upvotes: 1

Related Questions