Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    10

    Unanswered: counting unique records

    is there a way when viewing totals in a query and using the 'count' function, to count only unique records?

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465

    Re: counting unique records

    Originally posted by gutterz
    is there a way when viewing totals in a query and using the 'count' function, to count only unique records?
    Use the 'Sum' for the totals.
    Saludos
    Norberto

  3. #3
    Join Date
    Dec 2003
    Posts
    10
    thank you for the reply. i should have elaborated on the problem though.

    what i'm looking at is street addresses. this is not a numerical field<street numbers and street name combined>

    i can use 'sum' on the street number field but of course that adds the actual addresses, not the number of duplicate addresses.

    what i'm doing is grouping the steets by name and by street address so that i can see, by street, each unique street address and i need to know how many unique addresses there are per street....

    is there a way to do this?

  4. #4
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    167
    I think 'sum' is the wrong function, because sum adds numbers...
    Code:
     select Record_Id from table group by Record_Id having (count(Record_Id) = 1);
    board.perl-community.de - The German Perl-Community

  5. #5
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Originally posted by gutterz
    thank you for the reply. i should have elaborated on the problem though.

    what i'm looking at is street addresses. this is not a numerical field<street numbers and street name combined>

    i can use 'sum' on the street number field but of course that adds the actual addresses, not the number of duplicate addresses.

    what i'm doing is grouping the steets by name and by street address so that i can see, by street, each unique street address and i need to know how many unique addresses there are per street....

    is there a way to do this?
    In the query you agrup by name and by street address then count the fiel street address.
    Saludos
    Norberto

  6. #6
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    167
    Code:
    select street from table group street having (count(street) = 1);
    board.perl-community.de - The German Perl-Community

Posting Permissions

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