SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru dragonhawk's Avatar
    Join Date
    Apr 2002
    Location
    Melbourne
    Posts
    707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Syntax error?

    Hopefully someone can see where the error is in this select statement:

    SELECT browsers.group, COUNT(*) AS hitcount FROM hits JOIN browsers ON browsers.name = hits.browser WHERE type = 3 and browsers.useragent = '-GROUP-' AND DAYOFMONTH(hittime)=7 AND MONTH(hittime)=8 AND YEAR(hittime)=2005 AND uid='1' GROUP BY browsers.group ORDER BY hitcount DESC

    The table is named 'browsers' and contains the fields: bid bigint(20), name varchar(50), group varchar(255), useragent varchar(255)

    Error message when trying to run the SQL:

    You have an error in your SQL syntax near 'ON browsers.`name` = hits.browser WHERE type = 3 and browsers.useragent ' at line 2

    Any help is appreciated

  2. #2
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    239
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I think that you don't need the comma after SELECT browsers.group as the commas are for separating the items to be selected eg: SELECT b.isbn, b.title

  3. #3
    SitePoint Guru dragonhawk's Avatar
    Join Date
    Apr 2002
    Location
    Melbourne
    Posts
    707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    but it's selecting to items... browsers.group and hitcount, so you would need the comma there...

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    GROUP is a reserved word, try your query with backticks around it

    SELECT browsers.`group`, ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru dragonhawk's Avatar
    Join Date
    Apr 2002
    Location
    Melbourne
    Posts
    707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nope, that didn't work... still get the same error.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    oh, wait, i knew this one!!

    add the word INNER, it is not optional in your version (an older version, right?), although if you go to the mysql site and look at the syntax now, it is optional

    your version thinks you are trying to do a CROSS JOIN, so the ON clause is invalid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru dragonhawk's Avatar
    Join Date
    Apr 2002
    Location
    Melbourne
    Posts
    707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And we have a winner!! Thank you so much for your help

    Too bad reputation's not on any more, you would've gotten a positive from me for sure


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
  •