Using SQL query to find details of customers who ordered > x types of products

Question

Please note that I have seen a similar query here, but think my query is different enough to merit a separate question.

Suppose that there is a database with the following tables:

1. customer_table with customer_ID (key field), customer_name
2. orders_table with order_ID (key field), customer_ID, product_ID

Now suppose I would like to find the names of all the customers who have ordered more than 10 different types of product, and the number of types of products they ordered. Multiple orders of the same product does not count.

I think the query below should work, but have the following questions:

1. Is the use of count(distinct xxx) generally allowed with a "group by" statement?
2. Is the method I use the standard way? Does anybody have any better ideas (e.g. without involving temporary tables)?

Below is my query

select T1.customer_name, T1.customer_ID, T2.number_of_products_ordered
from customer_table T1
inner join 
(
    select cust.customer_ID as customer_identity, count(distinct ord.product_ID) as number_of_products_ordered
    from customer_table cust
    inner join order_table ord on cust.customer_ID=ord.customer_ID
    group by ord.customer_ID, ord.product_ID
    having count(distinct ord.product_ID) > 10
) T2
on T1.customer_ID=T2.customer_identity
order by T2.number_of_products_ordered, T1.customer_name

Solution 1

Isn't that what you are looking for? Seems to be a little bit simpler. Tested it on SQL Server - works fine.

SELECT customer_name, COUNT(DISTINCT product_ID) as products_count FROM customer_table
INNER JOIN orders_table ON customer_table.customer_ID = orders_table.customer_ID
GROUP BY customer_table.customer_ID, customer_name
HAVING COUNT(DISTINCT product_ID) > 10

Solution 2

You could do it more simply:

select

    c.id,
    c.cname,
    count(distinct o.pid) as `uniques`

from o join c
on c.id = o.cid

group by c.id

having `uniques` > 10

Comments

[Gus Melo]

This way you are counting distinct product id entries in the orders table, and thus reporting on how many unique products were ordered. And the "having" clause is making sure once the query is done, it filters out anything with less than 10 unique product ID orders.

[Gus Melo]

I think we're suggesting the same approach, except you forgot to include a report of how many distinct product_ids were ordered, and you are doing a filter on 2 or more unique product_ids, where he's requesting 10 or more.

[Andy]

Thank you. So the trick is to include the customer_name into the "group by" statement, thus cutting out the need for a temporary table and the extra join operation.

[Andy]

For some reason, the statement does not work in Oracle. But never mind. I think you have the same idea as Andrei in general. If I replace 'uniques' with count(distinct o.pid) then the query works.