Counting orders where customers purchased 1 item, >1 same color, >1 different color?

clawlan · #1 (**permalink**) 12-20-11, 13:47

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?

blindman · #2 (**permalink**) 12-20-11, 15:21

use Count(DISTINCT SKU) to determine whether multiple SKUs were ordered.

dm1 · #3 (**permalink**) 12-21-11, 04:57

Let's assume we have the following table and data:

Code:

CREATE TABLE orders
(
  id INT,
  item VARCHAR(30),
  sku VARCHAR(30)
);

INSERT INTO orders VALUES (1, 'chair', 'brown');

INSERT INTO orders VALUES (2, 'chair', 'green');
INSERT INTO orders VALUES (2, 'chair', 'green');

INSERT INTO orders VALUES (3, 'chair', 'brown');
INSERT INTO orders VALUES (3, 'chair', 'green');

Now to return orders with one item purchased (returns id = 1):

Code:

SELECT id FROM orders
GROUP BY id 
HAVING COUNT(*) = 1;

Orders with multiple items purchased but having the same type (2 green chairs):

Code:

SELECT id FROM orders
GROUP BY id 
HAVING COUNT(DISTINCT sku) = 1 AND COUNT(*) > 1;

Orders with multiple items having different types (green and brown chairs):

Code:

SELECT id FROM orders
GROUP BY id 
HAVING COUNT(DISTINCT sku) > 1 AND COUNT(*) > 1;

Dmitry

--
http://www.sqlines.com - SQL Articles and Free Online SQL Conversion Tool

Internet Services

Web Development

Digital Marketing

Consumer Tech