How to SUM() from an offset through the end of the table?

Question

If SELECT SUM(amount) FROM transactions ORDER BY order LIMIT 0, 50 sums the amount field for the first 50 records in a table, how do a sum all records after the first 50? In other words, I'd like to do something like SELECT SUM(amount) from transactions ORDER BY order LIMIT 50, *, but that doesn't work.

Answer 1

SELECT  SUM(amount)
FROM    (
        SELECT  amount
        FROM    transactions
        ORDER BY
                order
        LIMIT 50, 1000000000000
        ) q

Note that your original query:

SELECT  SUM(amount)
FROM    transactions
ORDER BY
        order
LIMIT 0, 50

does not do what you probably think it does. It is synonymous to this:

SELECT  a_sum, order
FROM    (
        SELECT  SUM(amount) AS a_sum, order
        FROM    transactions
        ) q
ORDER BY
        order
LIMIT   0, 50

The inner query (which would normally fail in any other engine but works in MySQL due to its GROUP BY extension syntax) returns only 1 records.

ORDER BY and LIMIT are then applied to that one aggregated record, not to the records of transactions.

Answer 2

The documentation advices to use an incredible large number as second parameter to LIMIT:

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

Answer 3

There is probably a more efficient way, but you could run a count query first, to retrieve total # of rows in your table:

SELECT count(*) FROM transactions

Stuff that into a variable and use that variable as your second argument for LIMIT. You could probably do this as a nested mysql query.