The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Invalid use of group function - HELP

I am, currently switching my website from an access DB to mySQL. I am getting an error "Invalid use of group function" when trying to execute the code below:

SELECT Max(Orders.date) AS MaxOfdate, Count(Orders.ProductsID) AS CountOfProductsID,
Year(date) AS myyear, Month(date) AS mymonth, Dayofmonth(date) AS myday
FROM Orders GROUP BY Year(date), Month(date), Dayofmonth(date), Orders.ProductsID
HAVING (((Orders.ProductsID)=1)) ORDER BY Max(Orders.date)

one problem is that dayofmonth isn't a valid function in Transact-SQL - try replacing it with Day (so doing Day(date) instead of Dayofmonth(date)

Another problem could be that date is a reserved word, so it won't like you using it as a column name. Try writing it as [date] in your query instead of date - so the above would actualy be Day([date])

That may solve your problem. Out of interest, why are you selecting the day, month and year seperately rather than selecting the date itself? The query would be more efficient if you just retrieved the date (though of course there may be good reason to select them seperately ).

RE: invalid grouop function

thanks for your reply. However I am using mySQL and as far as I know (i am not an expert so i may be wrong) dayofmonth is a valid function. I will try to enclose date in [] as [date].

I am doing the query this way because i need to get a broken down list of sales on a daily basis. If you can think of a better way to do it please do not hesitate. As I said I am not an expert but I am very keen in learning.

yeah, DAYOFMONTH() is a function. you're probably getting the error about GROUP BY b/c you have HAVING after GROUP BY. should be the other way around. actually, there shouldn't be HAVING (no reason for it). it should be "WHERE ProductsID=1". see what this does:

SELECT MAX(Orders.date) AS MaxOfdate, COUNT(Orders.ProductsID) AS CountOfProductsID,
YEAR(date) AS myyear, MONTH(date) AS mymonth, DAYOFMONTH(date) AS myday
FROM Orders WHERE ProductsID=1 GROUP BY date, Orders.ProductsID ORDER BY MaxOfdate

i don't think there's any reason to GROUP BY YEAR(date), MONTH(date), DAYOFMONTH(date) since GROUP BY date should do the same thing.

- Matt ** Ignore old signature for now... **
Dr.BB - Highly optimized to be 2-3x faster than the "Big 3." "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR