CODEWITHSUNDEEP
mysql
Euler13
Euler13

Reputation: 15

Finding the sum of values in a column for the last n rows

I'm trying to find the sum of values in a particular column for the last ten rows selected by some criteria and ordered by some column. I tried the obvious:

SELECT SUM(column) AS abc FROM table WHERE criteria ORDER BY column DESC LIMIT 10

However this seems to sum the entire column!?

So after playing around this seems to work:

SELECT SUM(column) AS abc FROM (SELECT column FROM table WHERE criteria ORDER BY column DESC LIMIT 10) AS abc

My questions...

Why doesn't the more intuitive approach work?

I could access the result by using $data[0], but I prefer to have some meaningful variable. So why do I need to do AS abc twice?

Is there a tidier/better way to do the job?

I'm quite inexperienced with SQL queries so I would really appreciate any help.

Upvotes: 1

Views: 1582

Answers (1)

Andrej
Andrej

Reputation: 7504

Because mysql runs query in the following order: 

FROM->WHERE->GROUP BY->HAVING->ORDER BY->LIMIT.

So limit will be applied after grouping and will filter groups but not ordinary rows.

Regarding abs twice: it's necessary to add alias for all derived queries. This is mysql rule.

Upvotes: 2

Related Questions