如何在php和mysql中获得每月销售总额？

Question

I have the following code which queries a database and presents the results:

<?php
include('connect.php');
$query="SELECT date1,month(date1) as month1,year(date1)as month2,(month( date1 )-month(date1 )) as difference,SUM(gtotal) AS daily_total,COUNT(id) AS num_orders FROM `sale` 
    where date1>='2012-01-28' AND date1<='2013-01-31' and tax_name='C.S.T. 4% ON SCRAP' and tin='Applied For' GROUP BY date1 ";

$qt=mysql_query($query);
echo mysql_error();
echo "<table border='1' cellspacing='1' cellpadding='0' width='400'>
    <tr valign='top' bgcolor=#363636 style='color:#ffffff;'>
        <td><b>No. of orders</b></td>
        <td><b>Date</b></td>
        <td><b>Month<br>(dt)</b></td>
        <td><b>Year<br>(dt2)</b></td>
        <td><b>difference</b></td>
    </tr>";
while($nt=mysql_fetch_array($qt))
{
    echo "<tr valign='top'>
        <td><font face='Verdana' size='2' >$nt[num_orders]</font></td>
        <td><font face='Verdana' size='2' >$nt[date1]</font></td>
        <td><font face='Verdana' size='2' >$nt[month1]</font></td>
        <td><font face='Verdana' size='2' >$nt[month2]</font></td>
        <td><font face='Verdana' size='2' >$nt[difference]</font></td>
         </tr>";

echo "<tr bgcolor=#c2c2c2>
    <td colspan=3></td>
    <td><b>Month Total</b></td>
    <td>$nt[daily_total]</td>
      </tr>";
}

echo "<tr valign='top'><td colspan=5></td></tr>";

$query1="SELECT * FROM `sale` where date1>='2012-01-28' AND date1<='2013-01-31' and  tax_name='C.S.T. 4% ON SCRAP' and tin='Applied For' and gtotal ";

$qt1=mysql_query($query1);
echo mysql_error();
$num=mysql_num_rows($qt1);
if($num)
{
    $p=0;
    $q=0;

    while($nt1=mysql_fetch_array($qt1))
    {
        $p=$p+$nt1['gtotal'];
        $q=$q+$nt1['subtotal'];
    }
}
echo "<tr valign='top' bgcolor=#c6c6c6>
    <td><font face='Verdana' size='2' >$C</font></td>
    <td><font face='Verdana' size='2' ><b>subtotal<br></b></font></td>
    <td><font face='Verdana' size='2' >$q</font></td>
    <td><font face='Verdana' size='2' ><b>gtotal<br></b></font></td>
    <td><font face='Verdana' size='2' >$p</td>
      </tr>";

echo "</table>";
?>

But I have hundreds of data items to calculate. Suppose I have 5 data items in January, they all should be seen in 5 rows and at the end of month there should be the month total and so on until 12 months. For example see this pic :- I hope you understand my problem and can help me.

Answer 1

mysql select sum group by date

SELECT MONTHNAME(o_date), SUM(total) 
FROM theTable
GROUP BY YEAR(o_date), MONTH(o_date)

or you calculate month sum in PHP like this:

$query="SELECT date1, month(date1) as month,year(date1) as year,SUM(gtotal) AS daily_total FROM `sale` GROUP BY date1 ORDER BY date1";
$qt=mysql_query($query);
$month_total=0;
$last_month_year='';
while($nt=mysql_fetch_array($qt))
{
    if($last_month_year=='')
        $month_total=$nt['daily_total'];
    elseif($last_month_year==$nt['month'].$nt['year'])
        $month_total+=$nt['daily_total'];
    else
    {
        $month_total=$nt['daily_total'];
        echo "Month total: ".$month_total."<br>";
    }
    echo $nt['date1'].": ".$nt['daily_total']."<br>";
    $last_month_year=$nt['month'].$nt['year'];
}