How To write a SQL Select Query for this problem?

Question

I have four tables: groups, users, votes, follows.

The structures of these tables are

groups

g_id      g_title      g_content
 1          t1         content1   
 2          t2         content2

users

u_id      u_groupid
 1         1
 2         1
 3         2
 4         2
 5         2

votes

v_id      v_userid      v_groupid      v_votes
 1         1             1              1
 2         1             2              1
 3         2             2              1
 4         3             2              1
 5         3             1              1

follows

f_id      f_userid      f_groupid
 1         1             1
 2         2             1
 3         2             2
 4         3             1
 5         3             2

The groups table records the basic information of a "group".

The users table keeps the relationship between users and groups, that is, if the user A belongs to groups B, then there will be a record in the user table.

The votes table means the supportive attitude that a user holds to a group.

If user A is interested in group A, then add a entry into the Follows table.

Now my problem is how to write one select statement to query the number of users, the number of votes and the number of followers of each group.

I want the query result likes this

g_id      num_users      unm_votes      num_followers    
 1           2              2                3
 2           3              3                2

By the way, my database is Mysql 5.0.51b.

Answer 1

If you want in 1 query, something like this will help you

SELECT g_id, 
       d1.num_users, 
       d2.unm_votes, 
       d3.num_followers 
FROM   groups gg 
       LEFT JOIN (SELECT g_id, 
                         COUNT(u.u_id) AS num_users 
                  FROM   groups g 
                         LEFT JOIN users u 
                           ON u.u_groupid = g.g_id 
                  GROUP  BY g_id) d1 
         ON d1.g_id = gg.g_id 
       LEFT JOIN (SELECT g_id, 
                         COUNT(v.v_userid) AS unm_votes 
                  FROM   groups g 
                         LEFT JOIN votes v 
                           ON v.v_groupid = g.g_id 
                  GROUP  BY g_id) d2 
         ON d2.g_id = gg.g_id 
       LEFT JOIN (SELECT g_id, 
                         COUNT(f.f_userid) AS num_followers 
                  FROM   groups g 
                         LEFT JOIN follows f 
                           ON f.f_groupid = g.g_id 
                  GROUP  BY g_id) d3 
         ON d3.g_id = gg.g_id 
GROUP  BY gg.g_id 

Answer 2

For the user count by group:

select g_id,count(u.uid) user_count  from groups g, users u 
where u.groupid = g.g_id
group by g_id

May want to read up on group by.