MySQL SUM

safra

Hi,

I have a search system. Each page returns 15 results with at the bottom a menu to navigate through the results pages. At the bottom of a specific column I would also like to display the total of this column which is the sum from record 1 in page 1 till the last record on the currently displayed page.

I am trying to get this sum through a seperate query. Something like this:

$sql = "SELECT SUM(ie), SUM(ge) FROM caja WHERE ".implode(" AND ",$array)." LIMIT 0, $tot";

It seems that the Limit part is completely ignored because it always returns the same values based on the specific WHERE clause. Am I right that LIMIT is ignored in this type of query? How can I solve this?

Thanks,
Raoul

devinemke

i assume that $array contains all of the primary keys of the records for that particular page of results?

$sql = 'SELECT SUM(ie), SUM(ge) FROM caja WHERE id IN(' . implode(',', $array) . ')';

this also asumes "id" is the name of the primary key field

mtmosier

LIMIT affects the number of rows returned. SUM only returns 1 row, so limit will have little affect on it.

If your where clause is using a list of unique ids you could limit the number of ids being included in the query.

$sql = 'SELECT SUM(ie), SUM(ge) FROM caja WHERE id IN(' . implode(',', array_slice($array, 0, $tot)) . ')';

Otherwise you might be able to do it with a temporary table, but that's probably more effort than it's worth. Unless the number of rows you're trying to sum up is huge I'd just retrieve the data (with LIMIT) and do the sum myself at that point.

Roger_Ramjet

Like mtmosier says, do it in the page. You must be itterating through the results and generating html to display each rwo, include a line that adds the field to a running total, and then display it when your loop finishes.

safra

Thanks for helping. I was just trying to get the sum straight from the query. Anyway, I did what you suggested. The sum is now calculated by going through the search results in php.