Hi.
On Fri 2003-01-31 at 10:44:58 -0500, [EMAIL PROTECTED] wrote:
> I am trying to get a count of Distinct IP's from my homemade hit-log
> database (don't ask). The db is MySQL. I'm trying this:
>
> SELECT DISTINCT ClientIP, COUNT(*), Month(TimeStamp),
> DayOfMonth(TimeStamp) FROM RedirectLog WHERE (TimeStamp BETWEEN
> '20030130000000' AND '20030131000000') GROUP BY Month(TimeStamp),
> DayOfMonth(TimeStamp)
>
> But it doesn't appear to be doing what I want. The Count per day is the
> same as it was when I didn't have "DISTINCT ClientIP, " in there, and I
> know from looking at the raw data that there are some duplicate
> ClientIP's in the data.
I am not sure what your query is supposed to do, because having
ClientIP without a grouping function doesn't make sense (you will get
a random ClientIP from each day and the chance is high, that it will
be distinct from the others).
But from your description it sounds as if you want something like
SELECT COUNT(DISTINCT ClientIP) AS amount,
MONTH(TimeStamp) AS month,
DAYOFMONTH(TimeStamp) AS day
FROM RedirectLog
WHERE TimeStamp BETWEEN 20030130000000 AND 20030131000000
GROUP BY month, day
The relevant part is using DISTINCT with COUNT, the other changes
(like the GROUP BY clause) were just cleanups while I was parsing
your query.
HTH,
Benjamin.
PS: Removed [EMAIL PROTECTED] from CC, because
#1 cross-posting is bad bad bad, and
#2 I don't want to post to a list that I don't know.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
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
