Sql Query to count same date entries

Question

All I want to count entries based on date.(i.e entries with same date.) My table is

You can see 5th and 6th entry have same date.

Now, the real problem as i think is the same date entry have different time so i am not getting what I want.

I am using this sql

SELECT COUNT( created_at ) AS entries, created_at
FROM wp_frm_items
WHERE user_id =1
GROUP BY created_at
LIMIT 0 , 30

What I am getting is this.

I want entries as 2 for date 2012-02-22

Answer 1

You must eliminate the time with GROUP BY

SELECT COUNT(*) AS entries, created_at
FROM wp_frm_items
WHERE user_id =1
GROUP BY DATE(created_at)
LIMIT 0 , 30

Answer 2

The reason you get what you get is because you also compare the time, down to a second apart. So any entries created the same second will be grouped together.

To achieve what you actually want, you need to apply a date function to the created_at column:

SELECT COUNT(1) AS entries, DATE(created_at) as date
FROM wp_frm_items
WHERE user_id =1
GROUP BY DATE(created_at)
LIMIT 0 , 30

This would remove the time part from the column field, and so group together any entries created on the same day. You could take this further by removing the day part to group entries created on the same month of the same year etc.

To restrict the query to entries created in the current month, you add a WHERE-clause to the query to only select entries that satisfy that condition. Here's an example:

SELECT COUNT(1) AS entries, DATE(created_at) as date 
FROM  wp_frm_items
WHERE user_id = 1 
  AND created_at >= DATE_FORMAT(CURDATE(),'%Y-%m-01') 
GROUP BY DATE(created_at)

Note: The COUNT(1)-part of the query simply means Count each row, and you could just as well have written COUNT(*), COUNT(id) or any other field. Historically, the most efficient approach was to count the primary key, since that is always available in whatever index the query engine could utilize. COUNT(*) used to have to leave the index and retrieve the corresponding row in the table, which was sometimes inefficient. In more modern query planners this is probably no longer the case. COUNT(1) is another variant of this that didn't force the query planner to retrieve the rows from the table.

Edit: The query to group by month can be created in a number of different ways. Here is an example:

SELECT COUNT(1) AS entries, DATE_FORMAT(created_at,'%Y-%c') as month
FROM wp_frm_items
WHERE user_id =1
GROUP BY DATE_FORMAT(created_at,'%Y-%c')