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
:D :D :D :D :D :D :D :D :D :D :( :( :( :( :( :o :o :o :o 8O 8O 8O 8O 8O 8O 8O 8O 8O

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