Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Posts
    3

    Question Unanswered: Counts on Subquerys

    OK I have a query that bring back a unique identifier and text value
    for instance

    Select Distinct global_id, Page_url from PageList

    global_id page_url
    ---------- ---------------------------------
    4306549 /true/attitudes.htm
    1460753 /true/current/answerthis.htm
    4303667 /true/current/answerthis.htm
    4306549 /true/current/answerthis.htm

    Now I want to do a count of the page_url.
    Can this be done in one SQL statement using a sub query of some kind

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SELECT COUNT(DISTINCT Page_url) from PageList

    ????
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2004
    Posts
    3
    Originally posted by Brett Kaiser
    SELECT COUNT(DISTINCT Page_url) from PageList

    ????


    Brett, this doesn't seem to accomplich what I'm looking for. It's only bringing a count back of one for answerthis.htm, when there are clearly three records.

    any suggestions

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm doing the best I can...but with the serial port to my telepathy device being blocked it's kind of difficult



    Maybe if you show us what the result you're looking for is suppose to be...DDl and sample raw data qould be very helpful to...

    But how about another shot in the dark

    Select global_id, Page_url, COUNT(*)
    from PageList
    GROUP BY global_id, Page_url

    And yes you clearly have 3, ummmm, rows...


    global_id page_url
    ---------- ---------------------------------
    4306549 /true/attitudes.htm
    1460753 /true/current/answerthis.htm
    4303667 /true/current/answerthis.htm
    4306549 /true/current/answerthis.htm
    Isn't the 1st row of your result set kind of ironic?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Apr 2004
    Posts
    3
    Originally posted by Brett Kaiser
    I'm doing the best I can...but with the serial port to my telepathy device being blocked it's kind of difficult



    Maybe if you show us what the result you're looking for is suppose to be...DDl and sample raw data qould be very helpful to...

    But how about another shot in the dark

    Select global_id, Page_url, COUNT(*)
    from PageList
    GROUP BY global_id, Page_url

    And yes you clearly have 3, ummmm, rows...


    The problem I'm having is that the unique global_id is breaking out each record on the group by. so when it is added i can't get a count of the page_url




    Isn't the 1st row of your result set kind of ironic?

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well was that it?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040

    Re: Counts on Subquerys

    How about this:

    SELECT page_url, count(page_url)
    FROM pagelist
    group by page_url

Posting Permissions

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