[RESOLVED] Count

Anyaer

Given a range of dates, say 2006-01-01 to 2006-12-31, I need to find the number of entries in the table for each day and the number of entries for each month (doesn't have to be one query). A final result would look something like:

2006-01 15 entries
2006-02 21 entries

etc., and

2006-01-01 2
2006-01-02 0
2006-01-03 1

etc.

I know I could just query the table via WHERE = <date range> and then use PHP to scan the array and tally the stuff I need but that seems inefficient. Is there a way for MySQL to do this?

Piranha

Something like this should work for the days assuming that you have a field with date in the database:

SELECT date_column, COUNT(*)
FROM table
WHERE date_column BETWEEN '2006-01-01' AND '2006-12-31'
ORDER BY date_column

The dates per month is a little bit more tricky, look at the date function in MySQL to see how it works and then you should be able to just change the ORDER BY part according to what it says.

bradgrafelman

I'm sure Piranha made a simple typo in that COUNT was meant to be COUNT() .

Anyaer

That helped. The final queries I used were:

SELECT cdate, COUNT(*)
FROM table
WHERE cdate BETWEEN '2006-01-01' AND '2006-12-31'
GROUP BY MONTH(cdate)

for the monthly query, and

SELECT cdate, source
FROM table
WHERE `cdate` between '2006-01-01' and '2006-12-31'
GROUP BY cdate";

for the daily one. When I tried to use ORDER BY instead of GROUP BY, MySQL kicked a syntax error back at me.

bradgrafelman

Yup, GROUP BY is what you'd want there. If the dates aren't in order, you can probably add an ORDER BY after the grouping statement.

Does that resolve your issue? If so, don't forget to mark this thread resolved.

Piranha

bradgrafelman wrote:
I'm sure Piranha made a simple typo in that COUNT was meant to be COUNT() .

Ops, now corrected. I also forgot the GROUP BY, must have been drunk or something...