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

Question

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?

zzlalani · Accepted Answer · 2014-02-10 07:26:53Z

1

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

answered Feb 10, 2014 at 7:26

zzlalani

24.6k16 gold badges48 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Raphaël Althaus · Accepted Answer · 2014-02-10 07:26:29Z

1

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

answered Feb 10, 2014 at 7:26

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

1 Comment

IlludiumPu36 Over a year ago

I had a problem with 'as cnt' returning "Unknown column 'attendance.cohort_fk' in 'where clause'"....

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related