2. PHPRunner General questions
  3. Running total
This topic is locked

Running total

 		4/2/2007 12:58:33 PM
PHPRunner General questions
J
 jim9 author

If you had a table like below, what's the code for the running total column?
orderid---------amount-----------runningtotal

1-----------------5.00----------------5.00

2-----------------7.00---------------12.00

3-----------------2.00---------------14.00

4-----------------5.75---------------19.75

etc
J
 		Jane 4/3/2007

Jim,
you can do it using Custom format on the "View as" settings dialog on the Visual Editor tab.

Here is a sample:

global $data,$conn;

$str = "select sum(amount) from TableName where amount<=".$data["amount"];

$rs = db_query($str,$conn);

$data = db_fetch_numarray($rs);

$value = $data[0];
G
 		Geoffww 11/3/2008

What would you suggest for multiplying two columns and having the sum in the last field of each row?
orderid---------amount-----------quantity--------------total

1-----------------5.00----------------3-------------------15.00

2-----------------7.00--------------- 9-------------------63.00

3-----------------2.00----------------17-----------------34.00

4-----------------5.75----------------1-------------------5.75

--------------------------------Total: 30--------Total: 117.75
I'm trying to figure out how to multiply amount and quantity fields to get a proper total. <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=34694&image=1&table=forumreplies' class='bbc_emoticon' alt=':unsure:' />
T
 		thesofa 11/3/2008

to get a total for each line, add a field in the SQL query for the table as follows

select

orderid,

amount,

quantity,

`amount`*`quantity` as `Total`

from

orderlines


I have assumed that the table is called Orderlines

now if the main table is called orders, the sql query for that will be

select order id,

date,

...........blah blah,

SUM(orderlines.amount * orderlines.quantity) AS `Goods Total`

from orders

LEFT OUTER JOIN orderlines on orders.order id = orderlines.id_order


you need a column in the child record for an identifier of id_order to reference back to the main order.

so the new SQL statement would be

select

orderid,

id_order,

amount,

quantity,

`amount`*`quantity` as `Total`

from

orderlines


To get the total on the list page, on the Field Order and Totals tab of PHPRunner, select the table you want totalled, in this case orderlines, and select the field you want totalled, in this case selct the created field we named Total in the first code snippet, in the Totals Type box at the bottom of the page, select "TOTAL2 as the group function.

there you go, a total in the list page for each order.
G
 		Geoffww 11/3/2008

That's terrific, I'll work on this tonight. Thanks!