Page 1 of 1
php mysql subtotal script required
Posted: Sun Mar 25, 2012 11:29 pm
by iamroming
Please help me getting the result of subtotal from mysql database
Note: Iam attaching the picture for example
Re: php mysql subtotal script required
Posted: Mon Mar 26, 2012 6:11 am
by social_experiment
You don't supply any code which will be more helpful than the image in this instance;
http://www.sql-tutorial.net/SQL-SUM.asp
Have a look at this url on how to use the SUM function
Re: php mysql subtotal script required
Posted: Mon Mar 26, 2012 6:46 am
by iamroming
Dear social experiment sorry i did not ask the quesiton with full details.
I have a table of vehicle ledger with colums vehicle no ,TR no.transaction date ,Dr,Cr,Cexp,amnt,balance , here my question is the ledger has debit and credit for different dates but i want the subtotal of dr,cr,cexp and amnt after every vehicle no . below iam writing the code , any help would be appreciated
NOTE: Iam attaching the picture of my output please take a look
Code: Select all
$sql="SELECT * FROM vehicleledger where date BETWEEN '$date1' and '$date2' and vehicleno BETWEEN '$Division' and '$Division1' order by vehicleno asc,updatetime asc
Re: help needed in subtotal row in php mysql
Posted: Wed Mar 28, 2012 2:05 am
by iamroming
I have a query wich (SELECT * FROM vehicle ) slects the vehicle and amount and date
like this
vehicle no. amount date
1111 150 2012-03-24
2222 350 2012-03-24
1111 150 2012-03-25
2222 350 2012-03-25
but i want the subtotal for every vehicle no for different dates like
vehicle no. amount date
1111 150 2012-03-24
1111 150 2012-03-25
SUB 300
2222 350 2012-03-24
2222 350 2012-03-25
SUB 700
I have used mysql GROUP BY with modifier ROLLUP but no results ,somebody help in this please
Re: php mysql subtotal script required
Posted: Wed Mar 28, 2012 2:31 am
by social_experiment
Code: Select all
SELECT SUM(`amount`)
FROM `vehicle`
WHERE `vechicle no` = 1111;
You can modify the example from the url;
Re: php mysql subtotal script required
Posted: Wed Mar 28, 2012 5:59 am
by iamroming
Dear Social thanks for ur reply , ithink ur not clearly getting what my question is
.when i retrevie the result with SUM('amount') where vehicleno='1111' and try to echo it
this will be displayed
vehicle no. amount date
1111 150 2012-03-24
2222 350 2012-03-24
1111 150 2012-03-25
2222 350 2012-03-25
Sub 300
but my question is how to insert a new row to display subtotal or (SUM('amount') where Vehicleno='1111' as u said ) between the vehiclenos as i am
using while(loop) to get array of results in php ,or is there any subquery in mysql to get the desired result?
pls help me
Re: php mysql subtotal script required
Posted: Wed Mar 28, 2012 6:09 am
by social_experiment
iamroming wrote:think ur not clearly getting what my question is
I concur
iamroming wrote:but my question is how to insert a new row to display subtotal or (SUM('amount') where Vehicleno='1111' as u said ) between the vehiclenos as i am
using while(loop) to get array of results in php ,or is there any subquery in mysql to get the desired result?
If you could clarify the above statement. I already gave an example of an sql query you can use for this;
Re: php mysql subtotal script required
Posted: Wed Mar 28, 2012 6:27 am
by iamroming
Dear Sir,
my query is SELECT vehicleno,amount,date,SUM(amount) from vehicles order by vehicleno asc,date desc
and it display this
Sub 1000
Sir i need the output like this
vehicle no. amount date
1111 150 2012-03-24
1111 150 2012-03-25
Sub 300 ------------------------- this line from either php or mysql code(iwant)
2222 350 2012-03-24
2222 350 2012-03-25
Sub 700 ------------------------- this line from either php or mysql code(iwant)
Grand = 1000
and my php code is
$sql="select vehicleno,amount, date,SUM('amount') from vehicles "
$res=mysql-query($res);
while($row=mysql_fetch_array($res))
{
//Result;
}
it echoes as
vehicle no. amount date
1111 150 2012-03-24
2222 350 2012-03-24
1111 150 2012-03-25
2222 350 2012-03-25
Grand 1000
help required
Re: php mysql subtotal script required
Posted: Wed Mar 28, 2012 6:39 am
by social_experiment
Code: Select all
<?php
while($row=mysql_fetch_array($res))
{
//Result;
//select the sum in here;
$sumQry = "SELECT SUM(amount) FROM vehicles WHERE vehicleno = '" . $row['vehicleno'] . "' GROUP BY vehicleno";
}
?>
Try the code above
Re: php mysql subtotal script required
Posted: Wed Mar 28, 2012 7:00 am
by iamroming
thanks a lot i will try it
Re: php mysql subtotal script required
Posted: Wed Mar 28, 2012 10:26 am
by iamroming
thanks social the script u supplied
<?php
while($row=mysql_fetch_array($res))
{
//Result;
//select the sum in here;
$sumQry = "SELECT SUM(amount) FROM vehicles WHERE vehicleno = '" . $row['vehicleno'] . "' GROUP BY vehicleno";
}
?>
Out puts the following
--------------------------------------------
vehicle no. amount date
1111 150 2012-03-24
Sub 300
1111 150 2012-03-25
Sub 300
2222 350 2012-03-24
Sub 300
2222 350 2012-03-25
Sub 300
sub after each row but iam confused how to write the code for the subtotal below the group by vehicleno
Re: php mysql subtotal script required
Posted: Wed Mar 28, 2012 2:03 pm
by shaunbip
Re: php mysql subtotal script required
Posted: Wed Mar 28, 2012 5:34 pm
by social_experiment
This is a quick solution which can probably be improved on. Replace the fields i used with the ones in your table
Code: Select all
<?php
$sql = "SELECT `vehicleno`, `amount`, `dateCol` FROM `vehicle`";
$qry = mysql_query($sql);
//
echo '<table>';
// count the amount of rows for each vehicle number
while ($row = mysql_fetch_array($qry)) {
$cQry = "SELECT COUNT(`id`) FROM `vehicle` WHERE `vehicleno` = '" . $row['vehicleno'] . "' ";
$cSql = mysql_query($cQry);
$cAry = mysql_fetch_array($cSql);
$rows = $cAry[0];
echo '<tr>';
echo '<td>' . $row['vehicleno'] . '</td>';
echo '<td>' . $row['amount'] . '</td>';
echo '<td>' . $row['dateCol'] . '</td>';
// add the amount in $row['amount'] into an array
// each time the iteration occurs
$total[] = $row['amount'];
// if the amount of rows of a specific vehicle number
// is equal to the amount of elements in the array it
// means the "last" record of a specific group is
// reached
if (count($total) == $rows) {
echo '<td>' . array_sum($total) . '</td>';
// clear the array contained in $total or the following
// amounts wont be displayed
$total = array();
}
echo '</tr>';
}
echo '</table>';
?>
Re: php mysql subtotal script required
Posted: Thu Mar 29, 2012 12:51 am
by iamroming
Thanks a lot for giving full , thank u very much