MySQL Count as {name} and WHERE {name} = X, Unknown column

I am trying to filter results based on the name assigned on count() and get this:

Unknown column 'total_submissions' in 'where clause'

SELECT SQL_CALC_FOUND_ROWS patient.*,count(patient_data.id) as total_submissions 
FROM patient 
LEFT JOIN patient_data ON (patient_data.patient_id = patient.id AND patient_data.date_finished IS NOT NULL) 
WHERE patient.doc_id = 2 AND total_submissions = 5 
GROUP BY patient.id 
ORDER BY patient.id DESC 
LIMIT 0,40

​x
 
Unknown column 'total_submissions' in 'where clause'​SELECT SQL_CALC_FOUND_ROWS patient.*,count(patient_data.id) as total_submissions FROM patient LEFT JOIN patient_data ON (patient_data.patient_id = patient.id AND patient_data.date_finished IS NOT NULL) WHERE patient.doc_id = 2 AND total_submissions = 5 GROUP BY patient.id ORDER BY patient.id DESC LIMIT 0,40​

After more research I did find out about not being able to use a column alias in the WHERE but I am unsure how to execute this query then. I assume it’s possible but how would I be able to filter the results based on the count() calculation of the query?

Answer

total_submissions is a column alias and the result of an aggregation function. You need to do that check in a havingclause:

SELECT SQL_CALC_FOUND_ROWS p.*, count(pd.id) as total_submissions 
FROM patient p LEFT JOIN
     patient_data pd
     ON pd.patient_id = p.id AND pd.date_finished IS NOT NULL 
WHERE p.doc_id = 2 
GROUP BY p.id 
HAVING total_submissions = 5 
ORDER BY p.id DESC 
LIMIT 0, 40;

 
SELECT SQL_CALC_FOUND_ROWS p.*, count(pd.id) as total_submissions FROM patient p LEFT JOIN     patient_data pd     ON pd.patient_id = p.id AND pd.date_finished IS NOT NULL WHERE p.doc_id = 2 GROUP BY p.id HAVING total_submissions = 5 ORDER BY p.id DESC LIMIT 0, 40;​

Notes:

Table aliases make the query easier to write and to read.
The condition on doc_id should still be in the WHERE clause.

Advertisement

Answer