Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

GROUP BY MONTH() Error

Codes here !

Moderators: macek, egami, gesf

GROUP BY MONTH() Error

Postby fam891 » Tue Nov 06, 2012 2:03 pm

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)
fam891
New php-forum User
New php-forum User
 
Posts: 2
Joined: Tue Nov 06, 2012 1:39 pm

Re: GROUP BY MONTH() Error

Postby MeroD » Tue Nov 06, 2012 11:59 pm

$query = "SELECT MONTH(EXPDATE),SUM(ORDTOTAL) ";
$query .= "FROM OEORDH ";
$query .= "WHERE TYPE='1' AND ORDTOTAL>'0' ";
$query .= "GROUP BY MONTH(EXPDATE)";
MeroD
New php-forum User
New php-forum User
 
Posts: 53
Joined: Wed Oct 10, 2012 12:14 am

Re: GROUP BY MONTH() Error

Postby fam891 » Fri Nov 09, 2012 8:35 pm

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.
fam891
New php-forum User
New php-forum User
 
Posts: 2
Joined: Tue Nov 06, 2012 1:39 pm

Re: GROUP BY MONTH() Error

Postby MeroD » Fri Nov 09, 2012 11:50 pm

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

Or: DATE_FORMAT('EXPDATE`, '%M')
MeroD
New php-forum User
New php-forum User
 
Posts: 53
Joined: Wed Oct 10, 2012 12:14 am

Re: GROUP BY MONTH() Error

Postby seandisanti » Tue Nov 13, 2012 1:30 pm

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.
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 838
Joined: Mon Oct 01, 2012 12:32 pm


Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 3 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.