Computation of totals from db results

[vyon13]

thanks to all who replied my post!!...
i have a query again guys... the query is how can i compute the sub-total salary,base from the result generated from the database..

example:
Name Salary
Von 1000
Eric 1000
Sub-total 2000 but this sub-total should automatically computed..


thanks in advance again!!

[ronverdonk]

Automatically computed, where? You cannot do it in the way you show it (listing the salaries and closing with a total as the last line) in MySQL.

But that is why you are in the PHP forum, because you'll have to do it in PHP: fetching each row, count the (sub)total, echo the line and, at the end, echo the computed total variable.

Ronald

[eddierosenthal]

thanks to all who replied my post!!...
i have a query again guys... the query is how can i compute the sub-total salary,base from the result generated from the database..

example:
Name Salary
Von 1000
Eric 1000
Sub-total 2000 but this sub-total should automatically computed..


thanks in advance again!!

if Von and Eric are in the same department, you can sum totals grouped by department

[vyon13]

Automatically computed, where? You cannot do it in the way you show it (listing the salaries and closing with a total as the last line) in MySQL.

But that is why you are in the PHP forum, because you'll have to do it in PHP: fetching each row, count the (sub)total, echo the line and, at the end, echo the computed total variable.

Ronald

$query = "SELECT tblemployee.emp_id, tblemployee.lname,  tblemployee.fname,  tblemployee.mname, tblemployee.bpay,  tblemployee.lpay,  tblemployee.status, tblemployee.bpay, tblpayroll.branch, tblpayroll.pay_id, tblpayroll.emp_id, tblpayroll.nday, tblpayroll.absent, tblpayroll.late, tblpayroll.ar, tblpayroll.lpag, tblpayroll.lmc, tblpayroll.lsss, tblpayroll.lemaf, tblpayroll.lother, tblpayroll.tricond, tblpayroll.cashb, tblpayroll.allow, tblpayroll.ot FROM tblpayroll LEFT JOIN tblemployee ON tblemployee.emp_id = tblpayroll.emp_id WHERE tblpayroll.branch='$branch' ORDER BY tblemployee.lname ASC"; 

if ($result){
 
while ($row = mysql_fetch_array($result, MYSQL_BOTH)){

echo "<tr><td>{$row['lname']}, {$row['fname']} {$row['mname']}.</b></td><td>{$row['nday']}</td><td>{$row['bpay']}</td><td>$cola</td><td>$ecola</td><td>{$row['ot']}</td><td>{$row['allow']}</td><td>{$row['lpay']}</td><td>{$row['absent']}</td><td>{$row['late']}</td><td>{$row['ar']}</td><td>{$row['lpag']}</td><td>{$row['lsss']}</td><td>{$row['lemaf']}</td><td>{$row['lmc']}</td><td>{$row['lother']}</td><td></td><td></td><td></td><td></td><td></td><td>$phil</td><td>{$row['cashb']}</td><td></td></tr>";
}
}

here my code, it fetch all the rows that equal to the tblpayroll.bran ch='$branch' , but the only problem is how can i get the subtotal of each fetch row let we say for the basic pay all rows with the same branch will be computed as sub-total,..

[coolsti]

I would imagine that what you wish to do could easilly be done within the MySQL query (thus letting MySQL do the work for you, instead of you needing to code this in PHP).

But then perhaps your post would find a better audience in the MySQL forum.

What I want to add here is that you could get much better help on the query if you also show the structure of the tables involved, not just the query like you did.

I always find it best to push as much of the bookkeeping to MySQL as possible, rather than post calculations in PHP, but this has limitations. If it causes the query to be too complicated (because of many table joins) and too slow, it would not be the best way.

Steve, Denmark

[ronverdonk]

What intrigues me is that you talk about "the subtotal of each fetch row". Seeing the SELECT you use, your result set is already organized by branch. In light of this statement: do you want a cumulative total for each row or just a total for the entire selected set.

A cumulative total can be specified in the SELECT itself but requires a rather complicate INNER JOIN with itself. So I'd stick with the PHP solution.

Ronald