Query and Group by

Question

i have some meetings with date and hours and examinator, on these meetings, i put a candidate. The candidate must pass the exam.

i have a table like that:

Exam | Meeting | Niche | Date     | Hour | Examinator | idCandidate
Math |    1    |   1   |2014-06-28|08:00 |    John    |     1
Math |    1    |   2   |2014-06-28|08:20 |    John    |     0
Math |    5    |   3   |2014-06-28|13:00 |    John    |     0
Math |    2    |   4   |2014-06-28|08:00 |    Will    |     0
Math |    2    |   5   |2014-06-28|08:20 |    Will    |     0
Math |    3    |   6   |2014-06-29|08:00 |    John    |     11
Math |    3    |   7   |2014-06-29|08:20 |    John    |     0
Math |    6    |   8   |2014-06-29|14:00 |    John    |     0
Bio  |    4    |   9   |2014-06-28|08:00 |    Bruce   |     0
Bio  |    4    |   10  |2014-06-28|08:20 |    Bruce   |     0
Bio  |    4    |   11  |2014-06-28|08:40 |    Bruce   |     0

What i would like is:

Exam |   Date   | Examinator | Nb Free Candidate
Math |2014-06-28|  John      |         2
Math |2014-06-28|  Will      |         2
Math |2014-06-29|  John      |         2
Bio  |2014-06-28|  Bruce     |         3

Actually, i want to display the number of free niche (meeting) order by exam, date and examinator.

Can you please help me?

Answer 1

You are probably looking for this GROUP BY query:

SELECT Exam, `Date`, Examinator, COUNT(*) AS nb_free_candidate
FROM tablename
WHERE idCandidate = 0
GROUP BY Exam, `Date`, Examinator

If your Date columns contains both date and time, you should use this instead:

SELECT Exam, DATE(`Date`), Examinator, COUNT(*) AS nb_free_candidate
FROM tablename
WHERE idCandidate = 0
GROUP BY Exam, DATE(`Date`), Examinator