CODEWITHSUNDEEP
mysqlsql
Muhammad Rashid
Muhammad Rashid

Reputation: 219

query is not working with DATE_FORMAT

select  d.doctorFName,d.doctorLName ,count(ap.appointmentID) totalap,
GROUP_CONCAT(DISTINCT  s.speciality) specialities
FROM  tbl_doctors d
INNER JOIN  tbl_doctor_speciality  ds ON (d.doctorID = ds.doctorID)
INNER JOIN tbl_speciality  s ON (s.spID = ds.spID)
Inner join tbl_appointment ap on (ap.doctorID = d.doctorID)
Inner join tbl_patients p on p.patientID = ap.patientID 
GROUP BY d.doctorID
where d.status = 1 and  DATE_FORMAT(ap.appDate, '%Y-%m') = '2013-10'

my below query is giving me error after where i am doing wrong please help me?

where d.status = 1 and  DATE_FORMAT(ap.appDate, '%Y-%m') = '2013-10'

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 'where d.status = 1 and ds.spID and DATE_FORMAT(ap.appDate, '%Y-%m') = '2013-10' at line 9

Upvotes: 0

Views: 50

Answers (2)

Rahul
Rahul

Reputation: 77866

You need to interchange these two lines

where d.status = 1 and  DATE_FORMAT(ap.appDate, '%Y-%m') = '2013-10'
GROUP BY d.doctorID

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

The where clause is in the wrong place. It goes before the group by:

select d.doctorFName,d.doctorLName ,count(ap.appointmentID) totalap,
       GROUP_CONCAT(DISTINCT  s.speciality) specialities
FROM  tbl_doctors d
INNER JOIN  tbl_doctor_speciality  ds ON (d.doctorID = ds.doctorID)
INNER JOIN tbl_speciality  s ON (s.spID = ds.spID)
Inner join tbl_appointment ap on (ap.doctorID = d.doctorID)
Inner join tbl_patients p on p.patientID = ap.patientID 
where d.status = 1 and  DATE_FORMAT(ap.appDate, '%Y-%m') = '2013-10'
GROUP BY d.doctorID;

Upvotes: 1

Related Questions