[RESOLVED] Counting Number of Related Records

tripwire8

I'm having some trouble figuring this one out. I have a database which contains a listing of employee names along with the results of completed exams they have done. So each record contains their name and the name of the exam they have completed.

What I'm trying to do is pull out how many exams each employee has done, and then email myself a message when all 15 exams have been completed.

I have tried grouping like this:

$sql = "SELECT employee, COUNT(employee) FROM examsData GROUP BY employee where employee = '$employee'";

and then counting the rows, but I'm not sure if I'm on the right track. Any ideas would be greatly appreciated.

laserlight

You need to decide if you want the count of a particular employee, or of all the employees.

$sql = "SELECT COUNT(employee)
	FROM examsData
	WHERE employee = '$employee'";

For the latter:

$sql = "SELECT employee, COUNT(employee)
	FROM examsData
	GROUP BY employee";

Or you might just want to list the employees who have completed 15 exams:

$sql = "SELECT employee
	FROM examsData
	WHERE COUNT(employee)=15
	GROUP BY employee";

tripwire8

Thanks laserlight, I tried using the third one you posted, but I keep getting an "Invalid use of group function" error. Is there another way to do that?

tomhath

You need to add a HAVING clause:

http://dev.mysql.com/doc/refman/5.0/en/select.html

 $sql = "SELECT employee, COUNT(employee) FROM examsData GROUP BY employee  HAVING COUNT(employee) = 15";

tripwire8

Thanks for the help, that works great!