SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Jan 2002
    Location
    Omaha, NE
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Query Question

    I have one table that holds some data from our weblogs (IP address, Agent, etc) along with a customer number and Site number. What I am looking to do is see to count the number of sites that an IP address appears in. The unique identifer for a site is the customer number and the site number. Right now I have a cold fusion script that gets the distinct IP's loops over that query and querys the table again looking for distinct custid, siteid that have the specific ip.

    Is there a way to do this in one query instead of looping over a query and making another query? What I want is a query that has an IP address and then the number of Custid,Siteid that the Ip address appears in.
    "Oh, you hate your job? Why didn't you say so? There's a support group for that.
    It's called EVERYBODY, and they meet at the bar."

    --Drew Carey

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    when you say "an IP address" and "the IP address" this suggests that you're interested in only one

    however, your description also says you have CF get the distinct IPs and then loop over them

    sounds like you want the counts for all IPs
    Code:
    select IP, count(distinct siteID, customerID) as sites
      from yourtable
    group
        by IP
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jan 2002
    Location
    Omaha, NE
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Error...

    Thanks for your help, you've pointed me in the right direction. The problem now is that I get an error.

    Incorrect syntax near ','.

    It looks like it doesn't like the count and the distinct working together. I'm using SQL Server 2000. Actually, SQL Server allows this to work but you can only have one column in the distinct. Any other ideas?
    "Oh, you hate your job? Why didn't you say so? There's a support group for that.
    It's called EVERYBODY, and they meet at the bar."

    --Drew Carey

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    oh you shoulda said sql server from the outset
    Code:
    select IP, count(*) as sites
      from (
           select IP
                , siteID
                , customerID
             from yourtable
           group
               by IP
                , siteID
                , customerID
           ) as foo
    group
        by IP
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Jan 2002
    Location
    Omaha, NE
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Excellent

    Brilliant! That worked. How did that work though? There are no Distincts in any of the queries? I'm assuming it has something to do with the group by's!

    Thanks Again!
    "Oh, you hate your job? Why didn't you say so? There's a support group for that.
    It's called EVERYBODY, and they meet at the bar."

    --Drew Carey

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    exactly

    the subquery -- actually it's called a derived table -- has a GROUP BY that results in one row per IP/site/customer

    then you just count the rows per IP
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •