mysql query calculation with join different tables

Question

I'm using MySQL 4.2, and I'm have 2 tables:

tbl_User stores USERID and REGCODE. REGCODE is the code user used for signup the service. tbl_Message stores messages posted by each of those users.

I need to query all users in tbl_User for the corresponding REGCODE to get total. Then, I would like to know is how many of these users have at least one entry in tbl_Message where their USERID matches up with AUTHORID. The last row is the % of user has post.

My goal is to calculate the percentage from two tables at last row, but I am not sure how to join tables in query to get the correct answer.

Tables chart: http://img526.imageshack.us/img526/6105/tablep.png

I asked this question yesterday, and @mathematical.coffee told me a simple query to get the result I want. It worked at first, but I found the query some issue with COUNT(tbl_User.USERID) and left join after I accepted the answer.

As you can see from this query, "COUNT(tbl_User.USERID) as totalUsers" is affected by the left join condition, thus the number of total users is wrong. Can someone help me this, thanks so much!

SELECT REGCODE,
       COUNT(tbl_User.USERID) as totalUsers, 
       COUNT(tbl_Message.MESSAGEID) as usersWhoPosted 
       COUNT(tbl_Message.MESSAGEID)/COUNT(tbl_User.USERID)*100 As Percent
FROM tbl_User 
LEFT JOIN tbl_Message ON tbl_User.USERID=tbl_Message.AUTHORID 
WHERE REGCODE BETWEEN 9001 AND 9008
GROUP BY REGCODE

Result:

REGCODE   totalUsers  usersWhoPosted  Percent
9001      763         233             30.5374
...   
9008      345         235             68.1159

COUNT(DISTINCT tbl_User.USERID) as totalUsers

ypercubeᵀᴹ
– ypercubeᵀᴹ

2012-01-31 00:24:40 +00:00
Commented Jan 31, 2012 at 0:24 — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jan 31, 2012 at 0:24

ypercubeᵀᴹ · Accepted Answer · 2012-01-31 00:35:44Z

1

You can modify your query to use DISTINCT. The usersWhoPosted count needs modification, too. Your query is counting messages posted, not users who posted:

SELECT 
    REGCODE,
    COUNT(DISTINCT tbl_User.USERID) AS totalUsers, 
    COUNT(DISTINCT tbl_Message.AUTHORID) AS usersWhoPosted 
    COUNT(DISTINCT tbl_Message.AUTHORID)/COUNT(DISTINCT tbl_User.USERID) * 100 
      AS Percent
FROM tbl_User 
   LEFT JOIN tbl_Message 
     ON tbl_User.USERID = tbl_Message.AUTHORID 
WHERE REGCODE BETWEEN 9001 AND 9008
GROUP BY REGCODE

You can also rewrite it like this:

SELECT 
    REGCODE,
    COUNT(*) AS totalUsers, 
    COUNT(HasPosted) AS usersWhoPosted 
    COUNT(HasPosted)/COUNT(*) * 100 
      AS Percent
FROM tbl_User 
   LEFT JOIN (SELECT 1 AS HasPosted) AS dummy 
     ON EXISTS
        ( SELECT *
          FROM tbl_Message
          WHERE tbl_User.USERID = tbl_Message.AUTHORID
        ) 
WHERE REGCODE BETWEEN 9001 AND 9008
GROUP BY REGCODE

edited Jan 31, 2012 at 0:35

answered Jan 31, 2012 at 0:27

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1177437 Over a year ago

Thanks @ypercube. the total user count is correct now. However, COUNT(tbl_Message.MESSAGEID) does not output the correct number for how many of these users have at least one entry in tbl_Message where their USERID matches up with AUTHORID. It seeming counting the total number of message by users.

Collectives™ on Stack Overflow

mysql query calculation with join different tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related