SQL Hit Count Help

[Guest_imported]

SELECT Count(Tracking.IP) AS CountOfIP, DatePart("m",[date]) & "-" & DatePart("d",[date]) & "-" & DatePart("yyyy",[date]) AS Expr1
FROM Tracking
GROUP BY DatePart("m",[date]) & "-" & DatePart("d",[date]) & "-" & DatePart("yyyy",[date]);

The above SQL statement works fine for what I am trying to except I only want to count unique IP addresses for each day.

Unique or Distinct won't work because each record is already unique. Any ideas?

 

[crufty]

' ... count( distinct Tracking.IP ) ... ' will achieve what you're trying to do.

 

[Guest_imported]

No, I get a Syntax Error (Missing Operator) message when I try to run it:

SELECT Count(Distinct Tracking.IP) AS CountOfIP, DatePart("m",[date]) & "-" & DatePart("d",[date]) & "-" & DatePart("yyyy",[date]) AS Expr1
FROM Tracking
GROUP BY DatePart("m",[date]) & "-" & DatePart("d",[date]) & "-" & DatePart("yyyy",[date]);

 

[crufty]

I don't know what DB you are using but 'count( distinct ... )' works in Oracle and, I believe, is
how the 'distinct' keyword is implemented in standard ANSI SQL.

I'm guessing but I'd say your group by clause is causing
the problem.

 

[tlbroadbent]

Some RDMS are not ANSI compliant so some syntax won't work. It appears that you are using Access. If so, try the following. The key is the sub-query which creates a derived table of unique IPs per day.

Note: I chose to use the Format function rather than datepart and concatenation. It is simpler. I also, order by date rather than the formatted date. Ordering by the formatted date will result in a non-date sequence.

Access Jet SQL:

SELECT
Count(IP) As DailyIPCount,
Format([date], "mm-dd-yyyyy&quot AS TheDate
FROM
(SELECT
Tracking.IP, [date]
FROM Tracking
GROUP By IP, [date]) As qry
GROUP BY [date];

May I recommend using the Access forum for Access questions. Check out fourum701 - Microsoft: Access Queries and JET SQL. If you're not using Access, please disregard. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[crufty]

Interesting that Access doesn't support 'count( distinct )' as it is a useful select construct.