SQL to get count by date

Question

I would like to get the number of rows in a table for each date. Right now I am doing:

SELECT SUM(COUNT(*)) FROM videos_videoview GROUP BY date

What would be the correct syntax for the above?

In addition, the date is stored not as a date, but as datetime (2012-01-25 10:26:20). How would I GROUP BY date here, ignoring the time?

possible duplicate of [How to group by date regardless of time?](http://stackoverflow.com/questions/8375800/how-to-group-by-date-regardless-of-time) — JohnFx, Feb 22 '12 at 04:48

score 7 · Accepted Answer · 2012-02-22T03:29:34.653

7

As per the documentation, the DATE() function truncates a timestamp down to a date.

select DATE(date),count(1)
from videos_videoview
group by DATE(date);

Honestly, this is very, very basic SQL. Please read through the documentation of whatever RDBMS you're using or get a basic book on SQL.

Documentation: read it. Love it. Use it.

edited Feb 22 '12 at 03:29

answered Feb 22 '12 at 03:14

score 1 · Answer 2 · answered Feb 22 '12 at 03:15

1

select date, count(*) from videos_videoview group by date;

Which DB are you using?

answered Feb 22 '12 at 03:15

Jer In Chicago

2 Answers2