MYSQL Select and group by date

Question

I'm not sure how create a right query to get the result I'm looking for. What I have is 2 tables. first has ID, Name columns and second has date and adminID, which is referenced from table 1 column ID. Now, what I want to get is basically number of times each admin loged in per day during the month.

ID  |  Date
------------------
4   |  2010/03/01
4   |  2010/03/04
4   |  2010/03/04
4   |  2010/03/05
4   |  2010/03/05

From structure like this one I want to get per day and month data so result would be similar to 1, 2, 2 march total 5 for admin 4:

ID  |  Date       | Count
--------------------------
4   |  2010/03/01 | 1
4   |  2010/03/04 | 2
4   |  2010/03/05 | 2

Answer 1

Try this:

  SELECT COUNT(*), a.name, DATE(l.date)
    FROM admin a
   INNER
    JOIN logins l ON l.admin_id = a.id
   WHERE l.date > start_date AND l.date < end_date
GROUP BY a.name, DATE(l.date)

Answer 2

EDIT: this is working, tested:

SELECT count(ID) AS day_count,ID,Date FROM Table GROUP BY Date;

Table:

id  date
4   2010-04-13
4   2010-04-13
4   2010-04-23
4   2010-04-11
4   2010-04-17
4   2010-04-17
4   2010-04-17

Outputs:

day_count   id  date
1   4   2010-04-11
2   4   2010-04-13
3   4   2010-04-17
1   4   2010-04-23

Answer 3

select id,count(*) as numRec ,date from tablename group by date order by newRec