CODEWITHSUNDEEP
mysqlsqlruby-on-railssum
djibouti33
djibouti33

Reputation: 12132

SUM multiple columns from multiple tables with filtering

Our users can like, save, share, and create content on both the mobile and the web. Each of those actions is represented by a table with a source column with values of either mobile or web.

I need to find our most active mobile users, represented by the total sum of likes, shares, saves, and creates.

User has_many Likes, Likes belong_to User
User has_many Shares, Shares belong_to User
User has_many Saves, Saves belong_to User
User has_many Creates, Creates belong_to User

// User Table
id | full_name | email | ...

// Likes, Share, Save, Create Table
id | user_id | source | ...

My ideal output would be something like:

// ordered by grand total
id | full_name | total likes | total saves | total creates | total shares | grand total

I can create a query for one of those actions: 

SELECT u.full_name, u.id, COUNT(*) as "Likes"
FROM users u, likes l
WHERE u.id = l.user_id and l.source = 'mobile'
GROUP BY full_name
ORDER BY COUNT(*) DESC

But I'm not sure how to sum up all of their actions across the different tables.

Our users table has likes_count, saves_count and create_count fields that get updated when each of those actions is performed, but they increase with mobile and web activity, and as stated above, I just need mobile. It's also missing a count for shares, and I was hoping to include that while trying to figure out who our most active mobile users are.

I'm not sure where to start, as it seems like it'll be a pretty hairy query.

We're using Rails, so I'd be fine with a console based solution (which will dump the SQL).

Upvotes: 0

Views: 105

Answers (2)

djibouti33
djibouti33

Reputation: 12132

Here's what I ended up with. Creating multiple virtual tables and joining them on the user's full name:

SELECT likes_table.full_name,
   COALESCE(likes_count,0) AS likes, 
   COALESCE(saves_count, 0) AS saves, 
   COALESCE(share_count,0) AS shares, 
   COALESCE(create_count,0) AS "create", 
   (COALESCE(likes_count,0) + COALESCE(saves_count,0) + COALESCE(share_count,0) + COALESCE(create_count,0)) AS TOTAL
FROM (
SELECT u.full_name, count(*) AS likes_count 
FROM users u, likes l
WHERE u.id = l.user_id 
AND l.source = 'mobile'
GROUP BY u.full_name) AS likes_table
    LEFT JOIN (
        SELECT u.full_name, COUNT(*) AS saves_count 
        FROM users u, saves s
        WHERE u.id = s.user_id
        WHERE s.source = 'mobile' 
        GROUP BY u.full_name) AS saves_table 
    ON likes_table.full_name = saves_table.full_name
    LEFT JOIN (
        SELECT u.full_name, COUNT(*) AS share_count
        FROM users u, shares sh
        WHERE u.id = sh.user_id
        AND sh.source = 'mobile'
        GROUP BY u.full_name) as shares_table
    ON likes_table.full_name = shares_table.full_name
    LEFT JOIN (
        SELECT u.full_name, COUNT(*) AS create_count
        FROM users u, create c
        WHERE u.id = c.user_id
        AND c.source = 'mobile'
        GROUP BY u.full_name) as create_table
    ON likes_table.full_name = create_table.full_name
ORDER BY TOTAL DESC

Upvotes: 0

mcalex
mcalex

Reputation: 6798

try: 

SELECT username, ident, SELECT SUM(COUNT(l.id)+count(sa.id)+...)  as Total
FROM(   
  SELECT u.full_name as username, u.id as ident, COUNT(l.id) AS  likes,
       count(sa.id) as saves, ...
  FROM users u, likes l, saves sa, ...
  WHERE u.id = l.user_id and l.source = 'mobile'
  AND (u.id = sa.user_id and sa.source = 'mobile')
  ...
  GROUP BY full_name)
ORDER BY Total DESC

Upvotes: 1

Related Questions