CODEWITHSUNDEEP
mysqlsqldatecount
user1563414
user1563414

Reputation: 91

narrowing down rows added on a date in mysql

I have the following statement:

select count(*), FirstAdded from db.table where date(FirstAdded) between '2013-07-01' and '2013-07-10' group by Firstadded order by count(*)

when i execute it, it returns data for every time a row has been added to the table.

1 | 2013-07-03 15:22:14
1 | 2013-07-03 15:23:14
1 | 2013-07-03 15:22:42
1 | 2013-07-03 15:45:29

I would like to arrogate this down to the amount of rows added daily (without the m:s time, just for the entire day). e.g.

345  | 2013-07-03 
7482 | 2013-07-04
1237 | 2013-07-05

Is this possible?

Upvotes: 0

Views: 76

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

This is a group by query:

select date(FirstAdded), count(*)
from db.table
group by date(FirstAdded);

The function date() converts the datetime value to just a date value, which seems to be what you want.

Your complete query would be:

select count(*), date(FirstAdded)
from db.table
where date(FirstAdded) between '2013-07-01' and '2013-07-10'
group by date(Firstadded)
order by count(*)

Upvotes: 3

Related Questions