3 Replies Latest reply: Oct 6, 2012 3:13 PM by rp0428 RSS

    simple select query

    966217
      Hi,

      I'm using database version 10.2.0.4.0
      I have a simple table here that goes like this:

      customerPreference (customerid, fruitid)

      I want to find out which customerid had bought all the different fruits.
      I did a distinct count of fruitid, but how do I choose which customerid has that distinct count value?

      select count(distinct fruitid) AS total_number_of fruits
      from customerPreference;

      Regards,
      Keith
        • 1. Re: simple select query
          sb92075
          963214 wrote:
          Hi,

          I'm using database version 10.2.0.4.0
          I have a simple table here that goes like this:

          customerPreference (customerid, fruitid)

          I want to find out which customerid had bought all the different fruits.
          I did a distinct count of fruitid, but how do I choose which customerid has that distinct count value?

          select count(distinct fruitid) AS total_number_of fruits
          from customerPreference;
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: simple select query
            rp0428
            >
            customerPreference (customerid, fruitid)

            I want to find out which customerid had bought all the different fruits.
            I did a distinct count of fruitid, but how do I choose which customerid has that distinct count value?

            select count(distinct fruitid) AS total_number_of fruits
            from customerPreference;
            >
            Well you got the first part done; the number of distinct fruits.

            So the next part is to get a list of customers and the number of distinct fruits each of them has purchased and then filter it using your query above. This is untested since you didn't provide table ddl or data.
            SELECT CUSTOMERID, COUNT(DISTINCT FRUITID) AS CUST_DISTINCT_FRUITS
            FROM CUSTOMERPREFERENCE
            GROUP BY CUSTOMERID
            HAVING COUNT(DISTINCT FRUITID) = (
            select count(distinct fruitid) AS total_number_of fruits
            from customerPreference);
            • 3. Re: simple select query
              966217
              Thanks for your solution. I couldn't get the logic and syntax right initially.
              Cheers. =D