CODEWITHSUNDEEP
mysql
Dizzy49
Dizzy49

Reputation: 1520

Why is this SUM not giving the the expected results?

I have a query that works great:

SELECT dl.*, d.*, dr.earn_rate
FROM list dl LEFT JOIN rates dr ON dr.id=dl.id AND dr.level = dl.level 
LEFT JOIN widgets d ON d.id=dl.id WHERE user_id=1 AND d.element=3 
ORDER BY dr.earn_rate DESC, dl.name LIMIT 20;

I want to quickly get the SUM of all of the earn_rate, and thought I could just put SUM in the SELECT, and leave everything else and it would work. However, it seems to ignore the LIMIT and sums all of the earn_rate as if there was not a LIMIT.

SELECT SUM(dr.earn_rate) FROM list dl 
LEFT JOIN rates dr ON dr.id=dl.id AND dr.level = dl.level 
LEFT JOIN widgets d ON d.id=dl.id WHERE user_id=1 AND d.element=3 
ORDER BY dr.earn_rate DESC, dl.name LIMIT 20;

Upvotes: 0

Views: 40

Answers (1)

Barmar
Barmar

Reputation: 780724

The LIMIT clause is applied to the result set, not the intermediate results used to form the results. If you want to sum the top 20 earnings, you need to use a subquery:

SELECT SUM(earn_rate)
FROM (
    SELECT dr.earn_rate
    FROM list dl
    LEFT JOIN rates dr ON dr.id=dl.id
    AND dr.level = dl.level
    LEFT JOIN widgets d ON d.id=dl.id
    WHERE user_id=1
      AND d.element=3
    ORDER BY dr.earn_rate DESC, dl.name
    LIMIT 20) x

Upvotes: 2

Related Questions