>a row gets inserted into this table
whenever a visitor uses a certain tool on the site
based on this approach your table does not have any entries for the days that no one
uses the certain tool on the site. As a result your query can not pull out records
which don't exist.
You could catter for this programmatically by a perl script. The script could do this:
create a temporary table
insert all records from your main table between TheStartDate and TheEndDate...
iterate through TheStartDate and theEndDate and add one record per day to the
temporary table.
The SQL statement should display count-1 for each day.
I don't have perl on my system so I can't help with the script I hope someone else can.
-Nasser.
---------- Jefferey Donnici <[EMAIL PROTECTED]> writes:
Return-Path: <[EMAIL PROTECTED]>
Received: from mx13.nyc.untd.com (mx13.nyc.untd.com [10.140.24.73])
by maildeliver04.nyc.untd.com with SMTP id AAA9CV2ZWAGWKH3J
for <[EMAIL PROTECTED]> (sender
<[EMAIL PROTECTED]>);
Sat, 18 Jan 2003 18:27:48 -0500 (EST)
Received: from web.mysql.com (web.mysql.com [213.136.49.183])
by mx13.nyc.untd.com with SMTP id AAA9CV2ZVA7XH6LJ
for <[EMAIL PROTECTED]> (sender
<[EMAIL PROTECTED]>);
Sat, 18 Jan 2003 18:27:47 -0500 (EST)
Received: (qmail 16262 invoked by uid 7797); 18 Jan 2003 23:24:32 -0000
Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm (http://www.ezmlm.org)
List-ID: <mysql.mysql.com>
Precedence: bulk
List-Help: <mailto:[EMAIL PROTECTED]>
List-Unsubscribe: <mailto:[EMAIL PROTECTED]>
List-Post: <mailto:[EMAIL PROTECTED]>
List-Subscribe: <mailto:[EMAIL PROTECTED]>
Delivered-To: mailing list [EMAIL PROTECTED]
Received: (qmail 16248 invoked from network); 18 Jan 2003 23:24:31 -0000
Message-ID: <[EMAIL PROTECTED]>
Date: Sat, 18 Jan 2003 15:24:29 -0800 (PST)
From: Jefferey Donnici <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
Subject: SQL Count Query
To: [EMAIL PROTECTED]
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
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
---------------------------------------------------------------------
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
