SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Jan 2002
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs down 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.

  2. #2
    SitePoint Addict
    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 - ]

  3. #3
    SitePoint Member
    Join Date
    Jan 2002
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Wink 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

  4. #4
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    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

  5. #5
    SitePoint Member
    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?

  6. #6
    SitePoint Addict
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •