Invalid use of group function - HELP

[maxnetpromo]

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)

Can someone help please.

[CrazyCrane]

Hi there,

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 ).

Hope that's of some use .

[maxnetpromo]

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.

Again thanks for your help

Adrian

[DR_LaRRY_PEpPeR]

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.

[maxnetpromo]

Thanks for your input man but I'm still getting the same error. "Invalid use of group functions".

Im confused... and it used to work evertime on ACCESS. What could be the difference?

[CrazyCrane]

Gah, my apologies - I misread your original post and thought you were using MS SQL, not MySQL - my mistake .