SQL find references with single occurrences

Question

I am trying to find a pure SQL solution for the following problem:

If I sell paint and paintbrushes, and keep a record of which colors are sold like this:

    select OrderNumber, Product, Product_Type, Qty from Sales

+-------------+------------+--------------+-----+
| OrderNumber |  Product   | Product_Type | Qty |
+-------------+------------+--------------+-----+
|        0001 | Red        | Paint        |   1 |
|        0001 | Blue       | Paint        |   2 |
|        0001 | Green      | Paint        |   1 |
|        0001 | Paintbrush | Brush        |   1 |
|        0002 | Green      | Paint        |   1 |
|        0002 | Paintbrush | Brush        |   1 |
|        0003 | Blue       | Paint        |   4 |
|        0003 | Red        | Paint        |   5 |
|        0003 | Paintbrush | Brush        |   1 |
|        0004 | Blue       | Paint        |   2 |
|        0004 | Paintbrush | Brush        |   1 |
|        0005 | Green      | Paint        |   1 |
|        0005 | Paintbrush | Brush        |   1 |
+-------------+------------+--------------+-----+

Is there a way to isolate orders where only one tin of paint was sold, and that tin of paint was green? I want to ignore paintbrushes because every order includes a paintbrush. So from the example dataset, the desired results would be orders #0002 and #0005 because on each of those orders the total qty of paint tins sold was 1, and those tins were green.

I think the answer lies possibly with a group by clause and some aggregate functions and possibly a sub query. However, i'm not exactly sure how to combine these to get the results i need.

Answer 1

First, filter out all brushes. This can be done in the where clause.

Then you need to process each order as a bunch of records. This can be achieved with group by OrderNumber, Product_Type which breaks your table into order groups. Then you can filter these groups in the having clause to find only those with a single sold item in them.

select OrderNumber
  from Sales
  where Product_Type <> 'Brush'
  group by OrderNumber, Product_Type
  having sum(qty) = 1
     and min(Product) = 'Green'