Re: (SQL) Count Distincts
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 '2003013000' AND '2003013100') 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 WHERETimeStamp BETWEEN 2003013000 AND 2003013100 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
RE: (SQL) Count Distincts
: : 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 : '2003013000' AND '2003013100') GROUP BY Month(TimeStamp), : DayOfMonth(TimeStamp) Looks like you are working on a project similar to http://traffic.handalak.com (login: demo password: demo). In the above example, you're trying to retrieve how many times a unique user (arguably) visited a site on certain month? In that case, you need to drop distinct and group it by IP address and specify the date inside WHERE clause. Simplified version would look something like: SELECT ClientIP, COUNT(*) FROM redirectLog WHERE MONTH(timeStamp) = MONTH(CURDATE()) GROUP BY clientIP; Good luck Sherzod - 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
(SQL) Count Distincts
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 '2003013000' AND '2003013100') 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. The MySQL docs don't have an example (that I could find) of what I'm trying to do. What am I doing wrong? (besides not getting enough sleep) TIA, Tab - 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
