CODEWITHSUNDEEP
mysql
user1177437
user1177437

Reputation: 79

MySQL query with calculation from two tables

I'm using MySQL 4.2.

I have 2 tables:

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

Upvotes: 0

Views: 2199

Answers (1)

mathematical.coffee
mathematical.coffee

Reputation: 56905

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).

Upvotes: 2

Related Questions