How do I count the number of visits to my site on a date with mysql?

I am storing all visits to my site in a table, I store the date, the page visited and a session id.

In theory, I can group somebody b开发者_如何学运维y their session id and this counts as 1 visit.

What I'd like to do however is go through the table and get the total of visits for each date. So it would group by the session id, and then group by the date.

ie:

SELECT DATE(added) as date, COUNT(*) FROM visits GROUP BY sessionID, date

This doesn't work as it retrieves then the total of visits for that session id, and the date.

My table structure looks a bit like this:

----------------------------------
| id | added | page | sessionid
----------------------------------

Any ideas?

My query gives me results that look like this:

2010-11-24 | 2

2010-11-24 | 14

2010-11-24 | 17

2010-11-24 | 1

While I'd be hoping for something more like a total of all those under the 1 date, ie:

2010-11-24 | 34

Each date contains the time which will be different for each request. If you use DATE in the GROUP BY clause just like you did in the SELECT clause, that will solve your problem.

By grouping by sessionID, it's going to create a row for every session. If instead of grouping by sessionID, you use COUNT(DISTINCT sessionID), that will contact the distinct number of session IDs for that date.

SELECT DATE(added) as date, COUNT(DISTINCT sessionID) as sessions FROM visits GROUP BY DATE(added)