Results 1 to 2 of 2

Thread: count distinct

  1. #1
    Join Date
    Feb 2002

    Unanswered: count distinct

    Hi. Got the following problem: i want to extract households (hhno) which bought different brands (brand) in a specific time period. i got a table with this time period, including hhno and brand. any hhno can have bought either the same brand all the time or different brands. i only want to extract those who bought the same brand all the time.

    hhno brand
    1 brand a
    1 brand a
    2 brand b
    2 brand b
    2 brand a

    in this example i want to extract household 1 cause it bought the same brand all the time /the result should be only one line with '1 brand a' irrespective of number of purchases).
    i thought of using a count function of brands bought. however, it is not possible to use a distinct operator within this function, so i really need help! thanks a lot

  2. #2
    Join Date
    Nov 2001
    Try this two query solution:

    SELECT Table1.hhno, Table1.brand
    FROM Table1
    GROUP BY Table1.hhno, Table1.brand;

    SELECT [Query1].[hhno]
    FROM Query1
    GROUP BY [Query1].[hhno]
    HAVING (((Count(Query1.brand))=1));


    P.S. Distinct is rather slow and takes a lot of PC resources.

Posting Permissions

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