Writing a Mathematical Formula in SQL?

Question

I have these tables: users, comments, ratings, and items

I would like to know if it is possible to write SQL query that basically does this:

user_id is in each table. I'd like a SQL query to count each occurrence in each table (except users of course). BUT, I want some tables to carry more weight than the others. Then I want to tally up a "score".

Here is an example:

user_id 5 occurs... 2 times in items; 5 times in comments; 11 times in ratings.

I want a formula/point system that totals something like this:

items 2 x 5 = 10; comments 5 x 1 = 5; ratings 11 x .5 = 5.5

TOTAL 21.5

This is what I have so far.....

SELECT u.users
     COUNT(*) r.user_id
     COUNT(*) c.user_id
     COUNT(*) i.user_id
FROM users as u
JOIN COMMENTS as c
     ON u.user_id = c_user_id
JOIN RATINGS as r
     ON r.user_id = u.user_id
JOIN ITEMS as i
     i.user_id = u.user_id
WHERE
    ????
GROUP BY u.user_id
ORDER by total DESC

I am not sure how to do the mathematical formula portion (if possible). Or how to tally up a total.

Final Code based on John Woo's Answer!

$sql = mysql_query("
        SELECT  u.username,
    (a.totalCount * 5) +
    (b.totalCount) +
    (c.totalCount * .2) totalScore
    FROM users u 
    LEFT JOIN
        (
            SELECT user_id, COUNT(user_id) totalCount
            FROM items 
            GROUP BY user_id
        ) a ON a.user_id= u.user_id
    LEFT JOIN
        (
            SELECT user_id, COUNT(user_id) totalCount
            FROM comments
            GROUP BY user_id
        ) b ON b.user_id= u.user_id
    LEFT JOIN
        (
            SELECT user_id, COUNT(user_id) totalCount
            FROM ratings
            GROUP BY user_id
        ) c ON c.user_id = u.user_id
    ORDER BY totalScore DESC LIMIT 10;");

Answer 1

Here's an alternative approach:

SELECT
  u.user_id,
  SUM(s.weight) AS totalScore
FROM users u
  LEFT JOIN (
    SELECT user_id, 5.0 AS weight
    FROM items
    UNION ALL
    SELECT user_id, 1.0
    FROM comments
    UNION ALL
    SELECT user_id, 0.5
    FROM ratings
  ) s
  ON u.user_id = s.user_id
GROUP BY
  u.user_id

I.e. for every occurrence of every user in every table, a row with a specific weight is produced. The UNIONed set of weights is then joined to the users table for subsequent grouping and aggregating.

Answer 2

Maybe this can help you,

SELECT  u.user_ID,
        (a.totalCount * 5) +
        (b.totalCount) +
        (c.totalCount * .2) totalScore
FROM    users u LEFT JOIN
            (
                SELECT user_ID, COUNT(user_ID) totalCount
                FROM items 
                GROUP BY user_ID
            ) a ON a.user_ID = u.user_ID
        LEFT JOIN
            (
                SELECT user_ID, COUNT(user_ID) totalCount
                FROM comments
                GROUP BY user_ID
            ) b ON b.user_ID = u.user_ID
        LEFT JOIN
            (
                SELECT user_ID, COUNT(user_ID) totalCount
                FROM ratings
                GROUP BY user_ID
            ) c ON c.user_ID = u.user_ID
ORDER BY totalScore DESC

but based on yur query above,thismay also work

SELECT  u.users
        (COUNT(*) * .5) +
        COUNT(*) +
        (COUNT(*) * 2) totalcore
FROM users as u
        LEFT JOIN COMMENTS as c
            ON u.user_id = c_user_id
        LEFT JOIN RATINGS as r
            ON r.user_id = u.user_id
        LEFT JOIN ITEMS as i
            ON i.user_id = u.user_id
GROUP BY u.user_id
ORDER by totalcore DESC

The only difference is by using LEFT JOIN. You will not use INNER JOIN in this situation because there are chances that user_id is not guaranteed to exists on every table.

Hope this makes sense