Post reply

need help with having clause

  • September 19, 2022 at 4:17 pm

    #4089473

    Hello,

    I am working on a query that needs to pull only those orders with 1 SKU and 1 Order Qty. It seems to me that it should just be as simple as adding a where clause for sum(order qty) = 1 and Having count(sku) =1, but it won't work. Here's what I need the data to be:

    Screenshot 2022-09-19 105616

    The yellow highlighted row is what I want, but for some reason I still get orders with more than 1 SKU on it. Need help on figuring out what else I need to do to remove those. Here's my code:

    SELECT DC_ORDER_NUMBER, SUM(ORDER_QTY) AS QTY FROM TABLE 
    GROUP BY DC_ORDER_NUMBER 
    HAVING SUM(ORDER_QTY) = 1 AND COUNT(DISTINCT SKU) = 1

    I've tried variations, where I took out order qty and just had the count on the SKU, didn't work. I'm grouping it only by order number so I still don't understand what could be the issue here. Tried many different ways, none seem to work. Any help on this would be great. Thanks!

  • September 19, 2022 at 6:28 pm

    #4089508

    The query works fine with the sample data. Do you have another example you can provide in a consumable form? Are you sure you're not grouping by SKU? The image of the data looks like the output of the query.

    SELECT a.DC_ORDER_NUMBER, SUM(a.ORDER_QTY) AS QTY 
    FROM 
    (
    VALUES 
    (27, 'CZ9857-104-M', 1),
    (29, 'CU8890-313-M', 1),
    (29, 'DH1990-610-L', 1)) AS a (DC_ORDER_NUMBER, SKU, ORDER_QTY)
    GROUP BY a.DC_ORDER_NUMBER 
    HAVING SUM(a.ORDER_QTY) = 1
    AND COUNT(DISTINCT a.SKU) = 1

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply