SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Error - Not sure how to get passed my error

    Hi Guys,

    This is the error im getting:

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM leads WHERE ProgramID = '1' AND `TimeStamp` >= '2013-01-01 00:00:00' AND `T' at line 1
    This is the query i am using:

    Code:
    SELECT ProgramName AS `name`, SUM(CASE WHEN Billable = 'PENDING' THEN 1 ELSE 0 END) AS `pending_leads`, SUM(CASE WHEN Billable = 'PASS' THEN 1 ELSE 0 END) AS `pass_leads`, SUM(CASE WHEN Billable = 'FAIL' THEN 1 ELSE 0 END) AS `fail_leads`, FROM leads WHERE ProgramID = '1' AND `TimeStamp` >= '2013-01-01 00:00:00' AND `TimeStamp` < '2013-01-31 23:59:99' GROUP BY ProgramName
    I think i know why im getting the error, its because i have the field TimeStamp wrapped in ' ' but if i remove them i still get the same error:

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM leads WHERE ProgramID = '1' AND TimeStamp >= '2013-01-01 00:00:00' AND Time' at line 1
    Can someone help me please resolve my problem.

    Thank you.
    You're Help Does Not Go Unnoticed, I have So Far Donated 25 GBP
    To Cancer Research UK As A Thank You To All The SitePoint
    Members That Have Helped Me In The PHP Forum Thank You!

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,128
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    It is likely the , in front of FROM

    Try this
    Code:
    SELECT ProgramName AS `name`, SUM(CASE WHEN Billable = 'PENDING' THEN 1 ELSE 0 END) AS `pending_leads`, SUM(CASE WHEN Billable = 'PASS' THEN 1 ELSE 0 END) AS `pass_leads`, SUM(CASE WHEN Billable = 'FAIL' THEN 1 ELSE 0 END) AS `fail_leads` FROM leads WHERE ProgramID = '1' AND `TimeStamp` >= '2013-01-01 00:00:00' AND `TimeStamp` < '2013-01-31 23:59:99' GROUP BY ProgramName

  3. #3
    SitePoint Addict
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you, that was exactly my problem

    Every now and again i need a fresh pair of eyes to see the problem. Thank you!
    You're Help Does Not Go Unnoticed, I have So Far Donated 25 GBP
    To Cancer Research UK As A Thank You To All The SitePoint
    Members That Have Helped Me In The PHP Forum Thank You!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    this error would've been a lot easier to spot if you had been using the leading comma convention, along with suitable formatting...
    Code:
    SELECT ProgramName AS `name`
         , SUM(CASE WHEN Billable = 'PENDING' THEN 1 ELSE 0 END) AS `pending_leads`
         , SUM(CASE WHEN Billable = 'PASS' THEN 1 ELSE 0 END) AS `pass_leads`
         , SUM(CASE WHEN Billable = 'FAIL' THEN 1 ELSE 0 END) AS `fail_leads`
         ,  -- a lot easier to see this, eh?
      FROM leads 
     WHERE ProgramID = '1' 
       AND `TimeStamp` >= '2013-01-01 00:00:00' 
       AND `TimeStamp`  < '2013-01-31 23:59:99' 
    GROUP 
        BY ProgramName
    do a search (on "leading comma") in this forum for other examples of people who have had exactly the same problem

    FYI, you're on the right track with your datetime range tests, but you're (incorrectly) omitting a small portion of the 31st

    do it this way instead --
    Code:
       AND `TimeStamp` >= '2013-01-01' 
       AND `TimeStamp`  < '2013-02-01'
    this technique works correctly for both DATE and DATETIME columns, with the added bonus that you never have to figure out leap years
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •