CODEWITHSUNDEEP
mysqlsqlsumsubquery
user1542894
user1542894

Reputation: 105

Select two tables and Sum column value

I need to perform a Select with 4 tables in it and get the top 5 scores by summing the reward values in tables preds and exact for every userID:

 -----table------columns-----

 1. tbl_users - `userID`
 2. matches   - `id` (there are other columns I use for the clauses)
 3. preds     - `uid` (same as `userID`)
                `mid` (same as `matches.id`)
                `reward` (this is the column I need to sum up)
 4. exact     - same structure as `preds`

Here's what I've been thinking:

SELECT ( 
      select sum(preds.reward) FROM preds, matches, tbl_users WHERE ...some clauses...              
) a,
( 
      select sum(exact.reward) FROM exact, matches, tbl_users WHERE ...some clauses...     
) b, 
      ...here I need to sum(a+b) as total..., 
      tbl_users.userID
FROM
      tbl_users
GROUP BY userID 
ORDER BY total DESC LIMIT 5

Upvotes: 0

Views: 633

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

I think the more typical approach to this query would be:

SELECT u.uid,
       ((select sum(p.reward) 
         from preds p
         where p.uid = u.uid
        ) +
        (select sum(e.reward) 
         from exact e
         where e.uid = u.uid
        )
       ) total
from tbl_users u join
     matches m
     on . . .
where . . .
order by total desc
limit 5;

This limits the complexity of the query. Depending on the nature of the where clause, using correlated subqueries can be a big performance gain.

Note: If users might be missing from one or both tables, you would need to take into account that the subqueries could return NULL.

Upvotes: 1

Psi
Psi

Reputation: 6783

Well, if you really need those subqueries instead of joining them, your only solutions seems to be another subquery:

SELECT combined.a, combined.b, combined.a + combined.b as sum, combined.userID
FROM (
    SELECT ( 
          select sum(preds.reward) FROM preds, matches, tbl_users WHERE ...some clauses...              
    ) a,
    ( 
          select sum(exact.reward) FROM exact, matches, tbl_users WHERE ...some clauses...     
    ) b, 
          tbl_users.userID userID
    FROM
      tbl_users
    GROUP BY userID 
    ORDER BY total DESC LIMIT 5
) as combined

After you limited the amount of records returned by the inner query to 5, this should not be a dramatic performance impact

Upvotes: 1

Related Questions