need help with a mysql query

noimad1

I've been trying to figure out how to write this query, but it is giving me some problems.

Basically I have a database of hits on a website. I need to pull the hits out of the database and count the number of hits per day for a given month.

Here is a sample record:

ID HIT # date
1 00001 2010-04-26 23:12:51

I can get the count for the whole month with:

SELECT count(hits.date) as hitsformonth FROM `hits` WHERE DATE_FORMAT(hits.date, '%m') = '12' AND  DATE_FORMAT(hits.date, '%y') = '2010'

That will give me all the hits for december 2010. However, how can I structure the query so instead of giving me a count for the entire month, it gives me a count for each day of that month for that year. I know I could do it with 30 different queries for each day of the month, but I'm sure there is a way to do this in one query.

I'm sure this is just a basic query, but for some reason I can't wrap my head around it to get it right...?

noimad1

Oh, I think i got it:


SELECT count(hits.date) as hitsformonth FROM `hits` WHERE DATE_FORMAT(hits.date, '%m') = '12' AND  DATE_FORMAT(hits.date, '%Y') = '2010'  GROUP BY DATE_FORMAT(hits.date, '%d')