php mysql subtotal script required
Moderator: General Moderators
-
iamroming
- Forum Commoner
- Posts: 27
- Joined: Sun Jan 31, 2010 4:02 am
- Location: Hyderabad, India
- Contact:
php mysql subtotal script required
Please help me getting the result of subtotal from mysql database
Note: Iam attaching the picture for example
Note: Iam attaching the picture for example
- Attachments
-
- post-141540-1196958950_thumb.jpg (14.78 KiB) Viewed 4897 times
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: php mysql subtotal script required
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
http://www.sql-tutorial.net/SQL-SUM.asp
Have a look at this url on how to use the SUM function
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
-
iamroming
- Forum Commoner
- Posts: 27
- Joined: Sun Jan 31, 2010 4:02 am
- Location: Hyderabad, India
- Contact:
Re: php mysql subtotal script required
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
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-
iamroming
- Forum Commoner
- Posts: 27
- Joined: Sun Jan 31, 2010 4:02 am
- Location: Hyderabad, India
- Contact:
Re: help needed in subtotal row in php mysql
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
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
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: php mysql subtotal script required
Code: Select all
SELECT SUM(`amount`)
FROM `vehicle`
WHERE `vechicle no` = 1111;
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
-
iamroming
- Forum Commoner
- Posts: 27
- Joined: Sun Jan 31, 2010 4:02 am
- Location: Hyderabad, India
- Contact:
Re: php mysql subtotal script required
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
.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
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: php mysql subtotal script required
I concuriamroming wrote:think ur not clearly getting what my question is
If you could clarify the above statement. I already gave an example of an sql query you can use for this;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?
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
-
iamroming
- Forum Commoner
- Posts: 27
- Joined: Sun Jan 31, 2010 4:02 am
- Location: Hyderabad, India
- Contact:
Re: php mysql subtotal script required
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
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
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: php mysql subtotal script required
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";
}
?>“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
-
iamroming
- Forum Commoner
- Posts: 27
- Joined: Sun Jan 31, 2010 4:02 am
- Location: Hyderabad, India
- Contact:
Re: php mysql subtotal script required
thanks a lot i will try it
-
iamroming
- Forum Commoner
- Posts: 27
- Joined: Sun Jan 31, 2010 4:02 am
- Location: Hyderabad, India
- Contact:
Re: php mysql subtotal script required
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
<?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
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: php mysql subtotal script required
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>';
?>“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
-
iamroming
- Forum Commoner
- Posts: 27
- Joined: Sun Jan 31, 2010 4:02 am
- Location: Hyderabad, India
- Contact:
Re: php mysql subtotal script required
Thanks a lot for giving full , thank u very much