Multiple group in mysql

Question

I have table, named "table_log".

Here is the structure

---------------------------------------
|id_log | user_id | login_date         |
---------------------------------------
|1      | 1       |2014-09-02 14:58:53 |
|2      | 1       |2014-09-03 24:18:53 |
|3      | 1       |2014-09-02 14:58:53 |
|4      | 1       |2014-09-01 02:28:53 |
|5      | 2       |2014-09-04 01:48:53 |
|6      | 3       |2014-09-05 04:58:53 |
|7      | 2       |2014-09-06 03:58:53 |
----------------------------------------

I want to count number of user each days. not how much log is.

As an example data, I want to show it like this:

---------------------------
|date        | user_number|  
---------------------------
|2014-09-02  | 1          |
|2014-09-03  | 1          |
|2014-09-04  | 5          |
---------------------------

Does any can help me? How to query my database?

Answer 1

SELECT date(login_date) AS date, count(DISTINCT user_id) AS user_count
  FROM table_log
 GROUP BY date(login_date)

The date function gives just the date-part of a datetime column, then it's a simple group by.

Answer 2

I dont get your ques, but u expect it?

2014-09-03 there is one record how user number will come 4 ? which scenario u asking?

SELECT date(login_date) AS date, count(date(login_date))
FROM tbl
GROUP BY date(login_date)