MySQL: get how many entrys for each id on each date?

Question

I have a table with an associate_id and date fields. I want to get how many the associates have for each date. For example if this was my Table:

date    associate_id
2009-07-08  10
2009-07-08  113
2009-07-09  113
2009-07-09  113

I want results like this:

date    associate_id    per_date
2009-07-08  10              1
2009-07-08  113             1
2009-07-09  113             2

I tried this but it wasn't right:

SELECT 
      associate_id, date, COUNT(date) as per_date
      FROM 
      tracking
      GROUP BY
      date 
      ORDER BY
      date

Also I am sure it needs to be a separate call but I also need a list of the DISTINCT associate_id's that were pulled.

Thanks!!

Answer 1

Try this:

Select date, associate_id, count(*) as per_date
From tracking
Group By assoicate_id, date
Order by date