Unanswered: Counting orders where customers purchased 1 item, >1 same color, >1 different color?
I am struggling with this query. In our system, items are organized by itemNo (collection of like items) and SKU (unique item within the itemNo).
For example, itemNo UL005 is a chair, and there are 5 SKUs for that ItemNo because there are 5 different color choices: UL005BL, UL005BR, etc.
I am trying to figure break out the orders into 3 buckets:
1. Orders with only 1 purchased (pretty straightforward. group by ItemNo where qty = 1)
2. Orders with >1 and like color (ie only 1 SKU in the order and qty>1)
3. Orders with >1 and different colors (multiple SKUs in same order)
NOTE: In an order that has >1 like color AND an additional color(s), defaults to #3. In other words, to fit into group #2, must have purchased >1 and only one color.
#1 is easy enough. But I am struggling with #2 and #3. Ideas?