If we have any SQL gurus out there (not mentioning any names, Gail :-) I 
have a simple problem I with undoubtedly a simple answer.
I have a log table:

Log_time | IP | Script_Name| Email | DK

I need to select based on the criteria Log_time between two dates, script 
name =  '/EN/travelsync/content/getthere.asp', and DK = 'TEST'
So far we get:

SELECT Log_time, IP, Script_Name, Email, DK
FROM uselog
WHERE
Log_time >  '2008-01-31' and Log_time < '2008-03-01' and Script_Name = 
'/EN/travelsync/content/getthere.asp' and DK = 'TEST'


I need to add a field indicating the number of times an email address occurs 
and not return multiple records for the same email address.

That's hard to explain, but hopefully I managed to do it.
If anyone here knows what I must do please let me know. Thanks!

--

Post a reply to this message

"Halbert" <hal### [at] gmailcom> wrote in message
news:480cfb5d$1@news.povray.org...
> If we have any SQL gurus out there (not mentioning any names, Gail :-)

Hmmm, am I hearing things?

> I
> have a simple problem I with undoubtedly a simple answer.
> I have a log table:
>
> Log_time | IP | Script_Name| Email | DK

Ok. Think I understand. It's a little complex, but not really difficult.
Do need some things clarifying.

Say you have 3 rows with the same Email, different IP, different Log time
and different script. (is that possible?)
What log time, IP, script do you want returning? The ones that match the
latest log-time? Oldest log time? Any one, doesn't matter?

Oh, what DB engine? Oracle, My SQL, SQL Server, other? What version?

If no one's helped by morning, I'll take a look, but it's 11pm here...

Post a reply to this message

The script name will not be a variable. But the email could come from 
different IPs at different times. They want to have counts for the number of 
times individuals used the getthere.asp script.
We are using MS SQL Server (2000 in this case)
Thanks again.


s
"Gail Shaw" <initialsurname@sentech sa dot com> wrote in message 
news:480cfff3@news.povray.org...
>
> "Halbert" <hal### [at] gmailcom> wrote in message
> news:480cfb5d$1@news.povray.org...
>> If we have any SQL gurus out there (not mentioning any names, Gail :-)
>
> Hmmm, am I hearing things?
>
>> I
>> have a simple problem I with undoubtedly a simple answer.
>> I have a log table:
>>
>> Log_time | IP | Script_Name| Email | DK
>
> Ok. Think I understand. It's a little complex, but not really difficult.
> Do need some things clarifying.
>
> Say you have 3 rows with the same Email, different IP, different Log time
> and different script. (is that possible?)
> What log time, IP, script do you want returning? The ones that match the
> latest log-time? Oldest log time? Any one, doesn't matter?
>
> Oh, what DB engine? Oracle, My SQL, SQL Server, other? What version?
>
> If no one's helped by morning, I'll take a look, but it's 11pm here...
>
>

Post a reply to this message

"Halbert" <hal### [at] gmailcom> wrote in message
news:480d0145$1@news.povray.org...
> The script name will not be a variable. But the email could come from
> different IPs at different times. They want to have counts for the number
of
> times individuals used the getthere.asp script.
> We are using MS SQL Server (2000 in this case)
> Thanks again.
>

Ok, so say this is the input (as a comma-seperated list). What do you want
as results?

Logtime, IP, Email
2008/01/01 10:15, 10.2.45.56, me@here
2008/01/01 10:25, 10.2.45.56, me@here
2008/01/01 16:15, 10.2.78.156, me@here
2008/01/02 12:15, 10.2.15.74, me@here
2008/01/02 12:15, 10.2.15.74, me@here
2008/01/02 12:17, 10.2.15.74, me@here
2008/01/02 12:19, 10.2.15.74, me@here
2008/01/01 12:15, 10.5.25.34, you@somewhere
2008/01/01 13:15, 10.5.25.34, you@somewhere
2008/01/01 14:15, 10.5.25.34, you@somewhere
2008/01/01 18:15, 10.5.25.38, you@somewhere

Post a reply to this message

> Logtime, IP, Email
> 2008/01/01 10:15, 10.2.45.56, me@here
> 2008/01/01 10:25, 10.2.45.56, me@here
> 2008/01/01 16:15, 10.2.78.156, me@here
> 2008/01/02 12:15, 10.2.15.74, me@here
> 2008/01/02 12:15, 10.2.15.74, me@here
> 2008/01/02 12:17, 10.2.15.74, me@here
> 2008/01/02 12:19, 10.2.15.74, me@here
> 2008/01/01 12:15, 10.5.25.34, you@somewhere
> 2008/01/01 13:15, 10.5.25.34, you@somewhere
> 2008/01/01 14:15, 10.5.25.34, you@somewhere
> 2008/01/01 18:15, 10.5.25.38, you@somewhere
>

me@here,7
you@somewhere,4

Post a reply to this message

Halbert wrote:
>> Logtime, IP, Email
>> 2008/01/01 10:15, 10.2.45.56, me@here
>> 2008/01/01 10:25, 10.2.45.56, me@here
>> 2008/01/01 16:15, 10.2.78.156, me@here
>> 2008/01/02 12:15, 10.2.15.74, me@here
>> 2008/01/02 12:15, 10.2.15.74, me@here
>> 2008/01/02 12:17, 10.2.15.74, me@here
>> 2008/01/02 12:19, 10.2.15.74, me@here
>> 2008/01/01 12:15, 10.5.25.34, you@somewhere
>> 2008/01/01 13:15, 10.5.25.34, you@somewhere
>> 2008/01/01 14:15, 10.5.25.34, you@somewhere
>> 2008/01/01 18:15, 10.5.25.38, you@somewhere
>>
> 
> me@here,7
> you@somewhere,4

That's easy, then.

select email, count(*) from logtable where '2008-...' < logtime and 
logtime < '2008...' group by email

The "group by" (and possibly "with rollup" and "having" clauses) is what 
you need to read up on.

If you need one of the IP addresses or other columns to come back, it's 
harder.
-- 
   Darren New / San Diego, CA, USA (PST)
     "That's pretty. Where's that?"
          "It's the Age of Channelwood."
     "We should go there on vacation some time."

Post a reply to this message

> That's easy, then.
>
> select email, count(*) from logtable where '2008-...' < logtime and 
> logtime < '2008...' group by email
>
> The "group by" (and possibly "with rollup" and "having" clauses) is what 
> you need to read up on.
>
> If you need one of the IP addresses or other columns to come back, it's 
> harder.


In reality, I need to select based on DK (a company identifier,)the script 
name and the date range but I think that it would be sufficient to return 
with only the email and the count.

Post a reply to this message

Halbert wrote:
>> That's easy, then.
>>
>> select email, count(*) from logtable where '2008-...' < logtime and 
>> logtime < '2008...' group by email
>>
>> The "group by" (and possibly "with rollup" and "having" clauses) is what 
>> you need to read up on.
>>
>> If you need one of the IP addresses or other columns to come back, it's 
>> harder.
> 
> 
> In reality, I need to select based on DK (a company identifier,)the script 
> name and the date range but I think that it would be sufficient to return 
> with only the email and the count. 

The "group by" lets you select on anything. (I.e., the "where" clause 
can refer to any column, including joins with other tables etc.)  But 
everything you *return* has to either be mentioned in the "group by" 
clause or has to be a summary function (like count(), sum(), avg(), 
etc).  (I forget what those kinds of functions are called in SQL, but...)

-- 
   Darren New / San Diego, CA, USA (PST)
     "That's pretty. Where's that?"
          "It's the Age of Channelwood."
     "We should go there on vacation some time."

Post a reply to this message

"Halbert" <hal### [at] gmailcom> wrote in message
news:480d087b$1@news.povray.org...
> > That's easy, then.
> >
> > select email, count(*) from logtable where '2008-...' < logtime and
> > logtime < '2008...' group by email
> >
> > The "group by" (and possibly "with rollup" and "having" clauses) is what
> > you need to read up on.
> >
> > If you need one of the IP addresses or other columns to come back, it's
> > harder.
>
> In reality, I need to select based on DK (a company identifier,)the script
> name and the date range but I think that it would be sufficient to return
> with only the email and the count.

Still very simple.

SELECT Email, Count(*)
FROM uselog
WHERE
Log_time >  '2008-01-31' and Log_time < '2008-03-01' and Script_Name =
'/EN/travelsync/content/getthere.asp' and DK = 'TEST'
group by Email

Post a reply to this message

"Darren New" <dne### [at] sanrrcom> wrote in message
news:480d2b39$1@news.povray.org...

> The "group by" lets you select on anything. (I.e., the "where" clause
> can refer to any column, including joins with other tables etc.)  But
> everything you *return* has to either be mentioned in the "group by"
> clause or has to be a summary function (like count(), sum(), avg(),
> etc).  (I forget what those kinds of functions are called in SQL, but...)

Aggregate.

Post a reply to this message