[RESOLVED] Help with MYSQL query

[1042]

Im trying to run this query to get totals on per month basis, so far this is what i have below;

//run the query to get the totals on per month basis//
			$query = "SELECT order_date, SUM(grand_total) FROM order_details WHERE order_status='completed' GROUP BY order_date order by order_date"; 

      $result = mysql_query($query) or die(mysql_error());

     // Print out results
     while($row = mysql_fetch_array($result)){

	 $order_date=$row['order_date'];

// show the results by month//	 
echo "$order_date = $". $row['SUM(grand_total)'];
echo "<br />";
}

this returns the following format:
2007-06-19 17:51:15 = $117.69
2007-06-19 17:58:57 = $76.99
2007-06-19 18:08:46 = $137.5
2007-06-19 18:22:25 = $94.19
2007-06-20 10:57:50 = $82.41
2007-06-21 15:09:21 = $94.19
2007-07-17 11:05:39 = $94.2
2007-08-19 17:52:52 = $26.5

but what i would like is to get the following format
july, 2007 = $total
June, 2007 =$total

in other words group the totals for the month for each month, hope i made sense.
what would be the best way to do this? thanks all for the help 🙂

[paulnaj]

Hi,

I think this should do it ...

<?php
$query = "
SELECT 
	DATE_FORMAT(order_date, '%M %Y') as order_month
,	SUM(grand_total) AS total_month
FROM order_details 
WHERE order_status = 'completed' 
GROUP BY order_month
ORDER BY order_date ASC
";

$result = mysql_query($query) or die(mysql_error());

// Print out results
while($row = mysql_fetch_array($result)){
	$order_date = $row['order_date'];
	// show the results by month//    

	echo $row['order_month'].' = $'. $row['total_month'].'<br />';
} 
?>

P.

[1042]

paulnaj wrote:

Hi,

I think this should do it ...

<?php
$query = "
SELECT 
	DATE_FORMAT(order_date, '%M %Y') as order_month
,	SUM(grand_total) AS total_month
FROM order_details 
WHERE order_status = 'completed' 
GROUP BY order_month
ORDER BY order_date ASC
";

$result = mysql_query($query) or die(mysql_error());

// Print out results
while($row = mysql_fetch_array($result)){
	$order_date = $row['order_date'];
	// show the results by month//    

	echo $row['order_month'].' = $'. $row['total_month'].'<br />';
} 
?>

P.

Thanks paulnaj, that did the trick 🙂