Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Location
    Cypress, CA
    Posts
    2

    Unanswered: Calculating a count of items, without counting duplicates

    I am developing several databases for a non-profit organization. The organization keeps track of visitors from various countries. I know the calculation for "counting" each visitor, but how do I count total number of countries represented?

    For example, the organization has had 450 visitors from 110 countries. I want each unique country to be counted only once, even though the country might appear in several records (each record is for one visitor). There are anywhere from 1-20 visitors from any given country in the database.

    The country is recorded in a text field by itself. I imagine this involves some sort of IF, THEN calculation.

    Any assistance would be greatly appreciated. Thanks.

  2. #2
    Join Date
    Jul 2003
    Location
    Cypress, CA
    Posts
    2

    someone please help!

    still need some guidance on this.

  3. #3
    Join Date
    Jul 2003
    Location
    Paris, France
    Posts
    1

    Re: someone please help!

    Originally posted by Munir
    still need some guidance on this.
    Assuming you have a Unique Id or a serial in that file, create a relationship on the Country field, using a SelfJoin FileA:Country::FileA:Country.

    Use this calculation
    CheckDupes = Case(Serial =:elfJoinerial,1,0)

    As this calculation tags the first occurance with a 1 and 0 for all others, create a SelfConstantRelationship (using a calc indexed =1 at both sides of your relationship) and use calculation
    NbofCountries =Sum(::Constant:CheckDupes)
    This would give you the total number of country in your db.

  4. #4
    Join Date
    Aug 2003
    Location
    Good old Europe
    Posts
    11
    Why not use a Count Distinct?

    select Count(Distinct Country) as CntCountries From Table;

    ?

    If your DB does not do Count Distinct its very easy to use it in a subquery...

    Select Count(Country) from
    (Select Distinct Country from Table);

Posting Permissions

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