Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Question 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?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    use Count(DISTINCT SKU) to determine whether multiple SKUs were ordered.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Dec 2011
    Posts
    13
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •