MySQL : “how many records per day?”
I just figured out a cool MySQL trick. I’ve been stupidly doing a very large loop through each month and possible day of the month to query my table for a run-rate per day… as in, “how many records were recorded for each day the data has been accepting data?”… No more large number of queries to get this data, just one:
SELECT DISTINCT(date(your_date_field_name)) AS thedate, count( * ) AS count
FROM your_table_name
GROUP BY thedate
ORDER BY thedate ASC
Results look like this:
2006-10-02 25 2006-10-03 12 2006-10-04 31 2006-10-05 131 2006-10-06 31 2006-10-07 130 2006-10-08 102
which is perfect.
Technorati Tags: mysql
Daniel:
I dont think you need the DISTINCT in there. the Group By should do that for you.
15 February 2007, 7:08 pmEureka123:
Finally found what I was looking for. Great!
echo “”;
15 March 2009, 4:59 amwhile($arr = mysql_fetch_assoc($res)) {
echo “”.$arr['thedate'].” => ”.$arr['count'].”";
}
echo “”;