SitePoint Sponsor |
|
User Tag List
Results 1 to 6 of 6
-
Jan 12, 2002, 09:03 #1
- Join Date
- Jan 2002
- Posts
- 3
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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)
Can someone help please.
-
Jan 12, 2002, 11:45 #2
- Join Date
- Jun 2001
- Location
- Newcastle, England
- Posts
- 268
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
Nick Wilson [ - email - ]
-
Jan 12, 2002, 19:10 #3
- Join Date
- Jan 2002
- Posts
- 3
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
Again thanks for your help
Adrian
-
Jan 12, 2002, 20:05 #4
- Join Date
- Jul 2001
- Location
- Missouri
- Posts
- 3,428
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Jan 12, 2002, 20:54 #5
- Join Date
- Jan 2002
- Posts
- 3
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Re see what it does
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?
-
Jan 13, 2002, 06:36 #6
- Join Date
- Jun 2001
- Location
- Newcastle, England
- Posts
- 268
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Gah, my apologies - I misread your original post and thought you were using MS SQL, not MySQL - my mistake
.
Bookmarks