Q:

MySQL SUM() with JOIN and LIMIT

The concerned schema and query is on SQL fiddle here http://sqlfiddle.com/#!2/312d3/16
The JOIN query below gets the result that I want. 
SELECT p.*, payer.username as payer_username, payee.username as
payee_username FROM (`payments_history` p)  JOIN (SELECT * FROM users)
AS payer ON `payer`.`user_id` = `p`.`payer_id`  JOIN (SELECT * FROM
users) AS payee ON `payee`.`user_id` = `p`.`payee_id`  ORDER BY
`p`.`created_timestamp`;

Doing a sum() on the "p.amount" column for all the rows works fine too.
SELECT SUM(p.amount)  FROM (`payments_history` p)  JOIN (SELECT * FROM
users) AS payer ON `payer`.`user_id` = `p`.`payer_id`  JOIN (SELECT *
FROM users) AS payee ON `payee`.`user_id` = `p`.`payee_id`  ORDER BY
`p`.`created_timestamp`;

But doing a sum() on the same column for the rows on each page (offset,limit) returns an empty result (I would like to have the total of the "amount" column for the rows on each page).
SELECT SUM(p.amount)  FROM (`payments_history` p)  JOIN (SELECT * FROM
users) AS payer ON `payer`.`user_id` = `p`.`payer_id`  JOIN (SELECT *
FROM users) AS payee ON `payee`.`user_id` = `p`.`payee_id`  ORDER BY
`p`.`created_timestamp` limit 0,2;

Also in the sum() query, when the offset starts from anything greater than 0 (try LIMIT 2,2), it returns an empty result.
What am I doing wrong?.
Thank you.

A:

Give this a try:
SELECT SUM(l.amount)  FROM (
  SELECT p.amount FROM payments_history p
  INNER JOIN users payer ON payer.user_id=p.payer_id
  INNER JOIN users payee ON payee.user_id=p.payee_id
  ORDER BY p.created_timestamp
  LIMIT 0,10
) l

I'm not sure if you plan to add other fields to the query, or have further conditions in a WHERE clause, but having those joins in there doesn't seem all that useful right now.

