I can't get it to work... It's an Access db - can I perhaps mail it to you?
Regards, Thomas -----Oprindelig meddelelse----- Fra: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]P� vegne af David Gregg Sendt: 12. april 2002 15:04 Til: [EMAIL PROTECTED] Emne: Re: [IMail Forum] OT: SQL syntax! I don't know what database you are going against but this would work on Oracle, MS-SQL, etc... select aa.week, count(aa.ip) from (select distinct a.week, a.ip from visitors a) aa order by 1 asc; Regards, David Gregg dgSoft Internet Services (949) 584-1514 -> Visit us at http://www.dgsoft.com -> Web hosting, Email outsourcing, Dedicated and shared infrastructure solutions. ----- Original Message ----- From: "Norman J. Nolasco" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, April 11, 2002 3:53 PM Subject: 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/
