Error - Not sure how to get passed my error

Databases
1

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:

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.

2

It is likely the , in front of FROM

Try this

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

Thank you, that was exactly my problem :slight_smile:

Every now and again i need a fresh pair of eyes to see the problem. Thank you!

4

this error would’ve been a lot easier to spot if you had been using the leading comma convention, along with suitable formatting…


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`
     ,  [COLOR="#FF0000"]-- a lot easier to see this, eh?[/COLOR]
  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 –


   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