Select max count per second

Question

I have a table like this

CREATE TABLE news
(
Id         INT NOT NULL auto_increment,
Headline   VARCHAR (255) NULL,
InDateTime   DATETIME NULL
)

How to get records count in per second(InDateTime)?

I'm using Mysql

---

Sample records:

578921,   'headline1', '8/20/2012 12:01:53 PM' 
578922,   'headline2', '8/20/2012 12:01:53 PM' 
578923,   'headline3', '8/20/2012 12:01:53 PM' 
578924,   'headline4', '8/20/2012 12:01:59 PM' 
578925,   'headline5', '8/20/2012 12:01:59 PM' 
578926,   'headline6', '8/20/2012 12:01:59 PM' 
578927,   'headline7', '8/20/2012 12:01:59 PM' 
578928,   'headline8', '8/20/2012 12:02:03 PM' 

Expected output:

time,                    count
'8/20/2012 12:01:53 PM', 3
'8/20/2012 12:01:59 PM', 4
'8/20/2012 12:02:03 PM', 1 

Answer 1

Have your tired this:

SELECT COUNT(id), InDateTime
FROM news
GROUP BY InDateTime

Answer 2

Here you want to group your result according to the time. For every time you want the number of rows. So you can use this query.

select time, count(*)
from news
group by time

Here group by time will create separate group of distinct time values. select time will select the time in the first column. And count(*) will give the count of the number of rows containing that value.

Its better you read this