Sql Query for counting records of the same date

Question

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

You can see that the 5th and 6th entries have the same date.

Now, the real problem, as I think, the same date entry has a different time, so I do not get 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 get is this.

I need entries like 2 for date 2012-02-22

Answer 1

The reason you get what you get is because you also compare time, down to the second. Thus, any recordings created from the same second will be grouped together.

To achieve what you really need , you need to apply the 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 will remove the time portion from the column field and thus merge all records created on the same day. You can do this further by deleting part of the day to group records created in the same month of the same year, etc.

To limit the query to elements created in the current month, you add a WHERE -clause to the query only to select records that satisfy this condition. Here is 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. COUNT(1) - the query part simply means Count each row , and you could also write COUNT(*) , COUNT(id) or any other field. Historically, the most effective method has been calculating the primary key, since it is always available in any index that the query mechanism can use. COUNT(*) used to leave an 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 option that did not force the query planner to retrieve rows from the table.

Edit : A monthly group request can be created in several 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') 

Answer 2

You must remove the time using 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 3

Oops, read it wrong.

Use GROUP BY DATE(created_at)

Answer 4

Try:

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