PHP MYSQL SUM Until a row then stop

Question

Basically I am creating a sort of checking account type query. Which would be fairly easy except I want it split into pages.

So I have a field that is called amount and it can be a positive or a negative number.

The problem is if I am on records 75-100 I need to have a query sum all of amount up until record 75 and give me the total. Then I can run my query for records 75-100 and loop through their amounts like so: (supposing that the sum up to and including 74 was $500)

record#    Amount    Total
75         25.00     525.00
76         35.00     560.00
77         40.00     600.00

etc.

What I was thinking was to get the idnum for record 75 and sum up until it finds it like so: SUM(case when idnum != $until then amount else 0 end)

However this just keeps going. It doesn't stop at 75 it just skips 75 then sums 76, 77, etc.. Any help would be appreciated.

Is `record` always sequential for each account or can there be gaps in the sequence? — Joachim Isaksson, Apr 20 '13 at 07:05
do you want to get sum of id from 1 to 75 or sum of total 75 records? — Yadav Chetan, Apr 20 '13 at 07:34
Hi, Joachim there will be gaps in the sequence. Punk, that would just get me the entire SUM, and it misses the point.Yadav, if I understand you right, it is the sum of 1-75 with a WHERE that will pick only certain ones out. — topedge, Apr 20 '13 at 14:23

Damith · Accepted Answer · 2013-04-20T09:44:38.160

0

SET @total=0;
SELECT
     recordno, 
     amount,    
     @total:= @total+ amount AS total
FROM table1 
LIMIT 75, 75

or

SELECT 
      cur.id, cur.amount, sum(prev.amount)
FROM table1 cur
left join table1 prev
    on cur.id >= prev.id
group by cur.id 
LIMIT 75, 75

here limit function start records from 76 to another 75 records, you can send this as parameter

edited Apr 20 '13 at 09:44

answered Apr 20 '13 at 09:33

Damith

62,401
13
102
153

How and where do I add: `WHERE group = '35'` – topedge Apr 20 '13 at 17:17
is there column named group in your table? have you try to write where clause with above solutions? – Damith Apr 20 '13 at 19:05
yes there is a column named group. where do I added that where? thanks – topedge Apr 20 '13 at 20:01
Oh I figured it out, it needs to have cur.group = '35' – topedge Apr 20 '13 at 20:51
Sorry to bug you again I instead decided to use your first option above with `SET @total=0;` but that is causing my loop issues. Here is my actual code" `$result2 = mysql_query("SET @total=0; SELECT *, @total:= @total+ `companyearned` AS `total` FROM `recordedhours` WHERE `group` = '$uid' ORDER BY `unixdate` DESC, `idnum` DESC LIMIT $from, $max_results"); while ($rowb = mysql_fetch_array($result2)) { //do stuff }` but I get the error: `Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in...` – topedge Apr 21 '13 at 04:35
@topedge you better ask new question for that – Damith Apr 21 '13 at 04:50
Okay here is the new question: http://stackoverflow.com/questions/16128462/php-mysql-set-gives-error-in-while-loop – topedge Apr 21 '13 at 06:08

PHP MYSQL SUM Until a row then stop

1 Answers1