Oops, that TSQL statement works for MS SQL7.0 or MS SQL2K. Which SQL database are you using? Access? Oracle? Something else?
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Thomas Testmann Sent: Friday, April 12, 2002 2:57 AM To: [EMAIL PROTECTED] Subject: SV: [IMail Forum] OT: SQL syntax! Thanks, but somehow it doesn't like the "COUNT(DISTINCT Visitors.IP)"... What should I do? Thomas :) -----Oprindelig meddelelse----- Fra: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]P� vegne af Norman J. Nolasco Sendt: 12. april 2002 00:53 Til: [EMAIL PROTECTED] Emne: RE: [IMail Forum] OT: SQL syntax! That will list the distinct combinations of week/ip addresses. He needs the count of distinct IPs within the week. I'm not 100% sure, but I think it's: SELECT Visitors.Week, COUNT(DISTINCT Visitors.IP) AS UniqueIPCount FROM Visitors GROUP BY Visitors.Week -Norm -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jerod M. Bennett Sent: Thursday, April 11, 2002 5:02 PM To: [EMAIL PROTECTED] Subject: RE: [IMail Forum] OT: SQL syntax! You need to add the DISTINCT parameter to your query. SELECT DISTINCT Visitors.Week, Visitors.IP FROM Visitors GROUP BY Visitors.Week, Visitors.IP; Jerod M. Bennett Director of Media Production Pixelpushers, Inc. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Thomas Testmann Sent: Thursday, April 11, 2002 1:18 PM To: [EMAIL PROTECTED] Subject: [IMail Forum] OT: SQL syntax! Hi, I have a counter on my website, which stores in a table, named 'Visitors', with the following fields: [Section],[Page],[Browser],[Country],[Date],[Time],[Year],[Month],[Week] ,[Da y],[Referent],[IP] I would then like to make a query on how many unique IPs there is, listed by week. However, I don't know how to do it, besides making two queries, 'IP1' and 'IP2', where it is the last mentioned that shows it. 'IP2', however makes a query on 'IP1', and I'd like to get a more "clean" query, that is, directly on the table. The two queries look like this: IP1: SELECT Visitors.Week, Visitors.IP FROM Visitors GROUP BY Visitors.Week, Visitors.IP; IP2: SELECT [IP1].[Week], Count([IP1].[IP]) AS DifferentIPs FROM IP1 GROUP BY [IP1].[Week]; I believe I should use some join or union, but can't get it to work... Really hope that somebody can help here! Thanks in advance! Regards, Thomas Testmann Please visit http://www.ipswitch.com/support/mailing-lists.html to be removed from this list. An Archive of this list is available at: http://www.mail-archive.com/imail_forum%40list.ipswitch.com/ Please visit the Knowledge Base for answers to frequently asked questions: http://www.ipswitch.com/support/IMail/ Please visit http://www.ipswitch.com/support/mailing-lists.html to be removed from this list. An Archive of this list is available at: http://www.mail-archive.com/imail_forum%40list.ipswitch.com/ Please visit the Knowledge Base for answers to frequently asked questions: http://www.ipswitch.com/support/IMail/ Please visit http://www.ipswitch.com/support/mailing-lists.html to be removed from this list. An Archive of this list is available at: http://www.mail-archive.com/imail_forum%40list.ipswitch.com/ Please visit the Knowledge Base for answers to frequently asked questions: http://www.ipswitch.com/support/IMail/ Please visit http://www.ipswitch.com/support/mailing-lists.html to be removed from this list. An Archive of this list is available at: http://www.mail-archive.com/imail_forum%40list.ipswitch.com/ Please visit the Knowledge Base for answers to frequently asked questions: http://www.ipswitch.com/support/IMail/ Please visit http://www.ipswitch.com/support/mailing-lists.html to be removed from this list. An Archive of this list is available at: http://www.mail-archive.com/imail_forum%40list.ipswitch.com/ Please visit the Knowledge Base for answers to frequently asked questions: http://www.ipswitch.com/support/IMail/
