CODEWITHSUNDEEP
mysql
IlludiumPu36
IlludiumPu36

Reputation: 4304

MySQL query with COUNT() to return records where field equals COUNT value

I have the following query which works fine:

SELECT *, COUNT(*) FROM attendance, cohort 
WHERE 
attendance.cohort_fk = cohort.cohort_pk 
AND 
attendance.cohort_fk = '$cohort' 
AND 
YEAR(attendance.attended) = '$year'
GROUP BY attendance.person_id ASC

In the table cohort, there is an int column 'attendance_pass'. Now I want to have another query similar to above that only returns records where COUNT(*) FROM attendance equals cohort.attendance_pass. Eg.

SELECT *, COUNT(*) FROM attendance, cohort 
WHERE 
attendance.cohort_fk = cohort.cohort_pk 
AND 
attendance.cohort_fk = '$cohort' 
AND 
YEAR(attendance.attended) = '$year'
AND 
COUNT() = cohort.attendance_pass
GROUP BY attendance.person_id ASC

How can I modify this second query to get just those records?

Upvotes: 1

Views: 770

Answers (2)

zzlalani
zzlalani

Reputation: 24364

You need to use HAVING

SELECT *, COUNT(*) FROM attendance, cohort 
WHERE 
attendance.cohort_fk = cohort.cohort_pk 
AND 
attendance.cohort_fk = '$cohort' 
AND 
YEAR(attendance.attended) = '$year'

GROUP BY attendance.person_id ASC

HAVING COUNT(*) = cohort.attendance_pass

Upvotes: 1

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

the aggregation functions must be in an having clause, not in the where clause.

By the way, you can use the alias.

And I don't think you can GROUP BY asc, you certainly mean a GROUP BY then an ORDER BY ... ASC

select *, count(*) as cnt from attendance
-- etc.

where
--etc.
having cnt = cohort.attendance_pass
GROUP BY attendance.person_id
ORDER BY attendance.person_id ASC

Upvotes: 1

Related Questions