I've got a table whose structure looks like this: CREATE TABLE IF NOT EXISTS calc_hits (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, hit_date DATETIME NOT NULL, PRIMARY KEY (id)) AUTO_INCREMENT = 1;
On a web site, a row gets inserted into this table whenever a visitor uses a certain tool on the site. The INSERT command looks like this: INSERT INTO calc_hits VALUES (null, SYSDATE()); This may be anywhere from zero to hundreds of times a day. I'd like to have a page that displays the values in this table in a meaningful way. I let the user choose the date range and then I select a count() of all the ID values and GROUP BY the date values. This works well, except that days with ZERO hits on them don't show up at all (nothing to group by)... here's the query I'm using: SELECT DATE_FORMAT(hit_date, '%c/%e/%Y') hit_date, COUNT(*) hit_count FROM homepage_hits WHERE hit_date >= <start_date> AND hit_date <= <end_date> GROUP BY date_format(hit_date, '%Y %c %e') ORDER BY date_format(hit_date, '%Y %m %d') DESC What I'd like is to change this so that a day that falls in that date range, but has ZERO hits for the day will still show up in the result set with the date and a HIT_COUNT value of 0. I've tried a number of different permutations of this query, but am not having much luck. Before I go the brute force route and handle it procedurally in my application (which could yield several dozen individual queries on the page), I'd like to see if anyone can help. I appreciate any feedback/assistance you may have. Regards, J Donnici __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
