Mysql distinct & row count help

i have a database i need help with I want to do a row count for the following cols

principal and failed_date

the idea is i can do a row count how many times a principal has appeared for each unique month

i was trying to do a PHP foreach but lost my self.

the querys i was using was going to be

SELECT DISTINCT principal FROM failed_inspections SELECT DISTINCT failed_dateFROMfailed_inspections

then do a foreach to do a row count.

any better solutions?

you can do it in single query, try this, hope it works for you

SELECT principal,COUNT(principal) FROM failed_inspections
GROUP BY MONTH(failed_date)

Thank you for the reply but this has come up and taken the first row principal and then added all the rows together.

I have managed to work a long winded PHP script to do it in the end but i really need to learn how to shorten it with the mysql query.

Assuming that failed_date is a DATE, maybe something like this:

SELECT 
    MONTH(failed_date) 
  , principal
  , COUNT(*) as appearances 
FROM failed_inspections
GROUP BY
    MONTH(failed_date) 
  , principal

If this isn’t what you need, maybe a data example could be useful to understand better what you need.