How to query the amount of orders that contain more than 1 unique product

Question

I have a sales table that contains columns such as id, order_number, product_number, other columns

id	order_no	product_number
1	300	10
2	300	10
3	300	11
4	301	12
5	301	13
6	302	10
7	303	11
8	304	10
9	304	10
10	305	13
11	305	15
12	305	17

How do I write a query that returns all orders that contain more than 1 distinct product_number? The result would be order_nos 300, 301 and 305

To take this a step further, imagine a product table that looks like so

product_number	category
10	1
11	2
12	4
13	3
14	5
15	3
16	6
17	3

I want to find all orders that have more than 1 distinct product category, in this case I want to find just orders 300 and 301 as 305's orders are all from category 3. Final step, how do I count these orders rather than just list them and compare this number to all orders? I want to be able see that there are 2 orders with 2 distinct categories and 5 orders in total.

Thanks in advance!

I have tried using a simple select query, joining the tables on product number and using HAVING count(distinct product_number) > 1, but if I change this to < 2 I get no results and if I remove this my results are the same, so clearly something has gone wrong!

Answer 1

Final step, how do I count these orders rather than just list them and compare this number to all orders? I want to be able see that there are 2 orders with 2 distinct categories and 5 orders in total.

You can use two levels of aggregation:

select count(*) cnt_total_orders,
    sum(case when cnt_product_numbers > 1 then 1 else 0 end) as cnt_multi_product_orders
from (
    select count(distinct product_number) cnt_product_numbers
    from mytable
    group by order_no
) t

This produces a resultset made of one row and two columns, with the first column showing the total count of orders and the second column counting only orders that have more than 1 distinct product.

On the other hand, if you want a resultset that shows how many orders have a given number of distinct products, we can slightly change the outer query:

select cnt_product_numbers, count(*) cnt_orders
from (
    select count(distinct product_number) cnt_product_numbers
    from mytable
    group by order_no
) t
group by cnt_product_numbers