MySQL query with calculation from two tables

Question

I'm using MySQL 4.2.

I 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. Thanks for your help!

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

Here is the query I am using:

Select 'Percentage',
  Sum(Case
    When tbl_User.REGCODE = 9001 And
   tbl_User.USERID = tbl_Message.AUTHORID Then 1 Else 0
  End) / Sum(Case When tbl_User.REGCODE = 9001 Then 1 Else 0 End) * 100.0,
  Sum(Case
    When tbl_User.REGCODE = 9002 And
   tbl_User.USERID = tbl_Message.AUTHORID Then 1 Else 0
  End) / Sum(Case When tbl_User.REGCODE = 9002 Then 1 Else 0 End) * 100.0,
  Sum(Case
    When tbl_User.REGCODE = 9003 And
   tbl_User.USERID = tbl_Message.AUTHORID Then 1 Else 0
  End) / Sum(Case When tbl_User.REGCODE = 9003 Then 1 Else 0 End) * 100.0,
  Sum(Case
    When tbl_User.REGCODE = 9004 And
   tbl_User.USERID = tbl_Message.AUTHORID Then 1 Else 0
  End) / Sum(Case When tbl_User.REGCODE = 9004 Then 1 Else 0 End) * 100.0,
  Sum(Case
    When tbl_User.REGCODE = 9005 And
   tbl_User.USERID = tbl_Message.AUTHORID Then 1 Else 0
  End) / Sum(Case When tbl_User.REGCODE = 9005 Then 1 Else 0 End) * 100.0,
  Sum(Case
    When tbl_User.REGCODE = 9006 And
   tbl_User.USERID = tbl_Message.AUTHORID Then 1 Else 0
  End) / Sum(Case When tbl_User.REGCODE = 9006 Then 1 Else 0 End) * 100.0,
  Sum(Case
    When tbl_User.REGCODE = 9007 And
   tbl_User.USERID = tbl_Message.AUTHORID Then 1 Else 0
  End) / Sum(Case When tbl_User.REGCODE = 9007 Then 1 Else 0 End) * 100.0,
  Sum(Case
    When tbl_User.REGCODE = 9008 And
   tbl_User.USERID = tbl_Message.AUTHORID Then 1 Else 0
  End) / Sum(Case When tbl_User.REGCODE = 9008 Then 1 Else 0 End) * 100.0
From tbl_User 
left Join tbl_Message
tbl_Message ON tbl_User.USERID = tbl_Message.AUTHORID 
Where tbl_Message.AUTHORID IS NOT NULL

Can you add the structures of the two tables please? That will help — Kangkan
– Kangkan, Commented Jan 30, 2012 at 6:07
It would help if you explained (in words) what you are trying to do -- e.g. "the percentage of messages in each user's inbox that they are the author of"? — mathematical.coffee
– mathematical.coffee, Commented Jan 30, 2012 at 6:10
I 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. — user1177437
– user1177437, Commented Jan 30, 2012 at 6:13

mathematical.coffee · Accepted Answer · 2012-01-30 06:34:00Z

2

You can do it like this:

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

This will give you something like (based on your picture):

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

It's much easier to have the REGCODE in the rows than the columns. Otherwise you're stuck with writing a huge unwieldy query for each REGCODE you're interested in like you are currently using.

If you really must have the table transposed (ie swap rows/columns), do it programmatically (eg in PHP if that's what you're using with MySQL).

answered Jan 30, 2012 at 6:34

mathematical.coffee

57.1k15 gold badges160 silver badges197 bronze badges

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

4 Comments

user1177437 Over a year ago

This is exactly the answer I'm looking for. Thanks so much!

user1177437 Over a year ago

I have a question regarding to the number of totalUsers. The COUNT(tbl_User.USERID) as totalUsers is affected by LEFT JOIN tbl_Message ON tbl_User.USERID=tbl_Message.AUTHORID. If I only COUNT(tbl_User.USERID), the number is different from use the LEFT JOIN. How can I overcome this issue? Thanks.

mathematical.coffee Over a year ago

ahh, you're right - I have to skip this for the next day or so because I'm busy, but I'll have a look at it after (or someone else is welcome to!)

user1177437 Over a year ago

no worries..I know you are busy. Thanks for all your helps. Another nice guy helped me solve this issue.

Collectives™ on Stack Overflow

MySQL query with calculation from two tables

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related