GROUP BY MONTH() Error

[fam891]

Hi all. I am attempting to pull data from my DB, and I would like it grouped by month. The two fields that I am pulling are ship date (EXPDATE - the data type is Date, and it is in the format yyyymmdd) and order amount (ORDTOTAL). I would like the order amounts for each month summed up, so that I can display the total order amounts for each month in an html table. I have tried the following code:

Code: Select all

$query = "SELECT EXPDATE,SUM(ORDTOTAL) ";
$query .= "FROM OEORDH ";
$query .= "WHERE TYPE='1' AND ORDTOTAL>'0' ";
$query .= "GROUP BY MONTH(EXPDATE)";

$result = mssql_query($query);

echo "<table border=\"1\"><tr align=\"center\"><td><b>Ship Date</b></td><td><b>$ Amount</b></td></tr>";
while($row = mssql_fetch_array($result))
{
  echo "<tr align=\"center\"><td>";
  echo date("Y/m/d", strtotime($row["EXPDATE"]));
  echo "</td><td>$" . number_format($row["ORDTOTAL"],2) . "</td></tr>";
}

Unfortunately, I keep getting this error:

Warning: mssql_query() [function.mssql-query]: message: Column 'OEORDH.EXPDATE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (severity 16) in C:\www\toship\index2.php on line 30

Warning: mssql_query() [function.mssql-query]: Query failed in C:\www\toship\index2.php on line 30

Warning: mssql_fetch_array(): supplied argument is not a valid MS SQL-result resource in C:\www\toship\index2.php on line 34

I used this code, without the SUM and GROUP BY functions, and it pulls the data properly. However, I get a bunch of duplicate entries for each day, and hundreds per month. That's why I would like to group them all by month. Does anyone see any ideas as to why this is not working?

Windows7-64, Apache (2.2.22), MSSQL (2008 R2), PHP (5.2.17)

[MeroD]

$query = "SELECT MONTH(EXPDATE),SUM(ORDTOTAL) ";
$query .= "FROM OEORDH ";
$query .= "WHERE TYPE='1' AND ORDTOTAL>'0' ";
$query .= "GROUP BY MONTH(EXPDATE)";

[fam891]

Thanks for the reply. When I use the query you posted, I don't get an error anymore. However, it still isn't producing the correct results. In the data, there are 8 months with multiple dollar values. The query is returning the correct sum of all of the values, but it is lumping all of the months into two rows. Could it be the way that the date is stored in the database?, and perhaps the Month() function isn't working properly? The date is stored as yyyymmdd.

[MeroD]

Try: GROUP BY YEAR(EXPDATE), MONTH(EXPDATE)

Or: DATE_FORMAT('EXPDATE`, '%M')

[seandisanti]

You're having issues because you're pulling a scalar value, and grouping on an aggregate. Change your select to read '$query = "SELECT MONTH(EXPDATE),SUM(ORDTOTAL) ";' etc, and you should be fine, except that you're probably going to have bad data if you have data for 11/11 and 11/12. so you could select and group by YEAR(EXPDATE),MONTH(EXPDATE). You also don't need to include your sum(ORDTOTAL) in the group by.