SQL query using case and Group by aggregation

Question

I have the following situation:

1 Table for a Warehouse where I have: the ProductID, the qty and the address 1 Table for the Stores Where I have: The ProductID and qty 1 table for the Stores Storage addresses

What I need to know is if a product is "in Stock" or "out of Stock" using the criteria that for a product to be considered "out of stock" it has to have qty >=0 or >20k in both tables (stores and warehouse)

What I have tried is the following:

SELECT Product_Cod, qty_wh, QtyStore,
    CASE
        WHEN sum(QtyStore) BETWEEN 1 AND 20000 THEN 
                                CASE
                                        WHEN qty_wh > 0 THEN 'In Stock'
                                        WHEN qty_wh = 0 THEN 'In Stock'
                                END
        WHEN sum(QtyStore) = 0 OR sum(QtyStore) > 20000 THEN
                                CASE
                                        WHEN qty_wh <= 0 THEN 'Out of Stock'
                                        WHEN qty_wh > 0 THEN 'in Stock'
                                END
    END AS [Result]
FROM tb_Products, tb_qtyStock, tb_StoreProdAddress
WHERE Product_Cod = wh_CodProduct
AND Product_Cod = Store_CodProduct
GROUP BY Product_Cod, qty_wh, QtyStore 
ORDER BY Product_Cod

Which outputs:

   ProdCod qty_wh QtyStore   Result
    10026   26      0       In Stock
    10026   26      1       In Stock
    10026   26      2       In Stock
    10070   25      0       In Stock
    10070   25      2       In Stock
    10070   25      3       In Stock
    10071   20      0       In Stock
    10071   20      1       In Stock
    10071   20      29991   In Stock
    10072   32      0       In Stock
    10072   32      1       In Stock
    10072   32      29978   In Stock
    10204   0       0       Out of Stock
    10204   0       1       In Stock
    10204   0       4       In Stock
    10204   0       29996   Out of Stock

But I can't make it work like I want because the QtyStore column doesn't SUM() it repeats because the tb_StoreProdAddress is used for several stores so Multiple stores can have the same product but each store may have a different address for that product, but the address for given product in the warehouse is the same.

There is a way to SUM() the QtyStore column and just Group the qty_wh ???

Yhe reason you can't use sum on it is because it's in the group by clause. Have you tried taking the QtyStore column out of the group by clause? — Zohar Peled
– Zohar Peled, Commented Aug 7, 2015 at 12:42

GullitsMullet · Accepted Answer · 2015-08-07 12:46:39Z

1

You could try maxing qty_wth then summing QtyStore like so:

SELECT Product_Cod, max(qty_wh) as qty_wh, sum(QtyStore) as QtyStore,
    CASE
        WHEN sum(QtyStore) BETWEEN 1 AND 20000 THEN 
                            CASE
                                    WHEN qty_wh > 0 THEN 'In Stock'
                                    WHEN qty_wh = 0 THEN 'In Stock'
                            END
        WHEN sum(QtyStore) = 0 OR sum(QtyStore) > 20000 THEN
                            CASE
                                    WHEN qty_wh <= 0 THEN 'Out of Stock'
                                    WHEN qty_wh > 0 THEN 'in Stock'
                            END
END AS [Result]
FROM tb_Products, tb_qtyStock, tb_StoreProdAddress
WHERE Product_Cod = wh_CodProduct
AND Product_Cod = Store_CodProduct
GROUP BY Product_Cod
ORDER BY Product_Cod

answered Aug 7, 2015 at 12:46

GullitsMullet

3583 gold badges11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fabienne B. · Accepted Answer · 2015-08-07 13:09:17Z

to avoid to make the same operation multiple times, you can do your calcul in subquery :

SELECT Product_Cod,
       qty_wh,
       QtyStore,
       CASE
          WHEN QtyStore BETWEEN 1 AND 20000
          THEN
             CASE
                WHEN qty_wh > 0 THEN 'In Stock'
                WHEN qty_wh = 0 THEN 'In Stock'
             END
          WHEN QtyStore = 0 OR QtyStore > 20000
          THEN
             CASE
                WHEN qty_wh <= 0 THEN 'Out of Stock'
                WHEN qty_wh > 0 THEN 'in Stock'
             END
       END AS [Result]
    FROM (SELECT Product_Cod,
               MAX (qty_wh) AS qty_wh,
               SUM (QtyStore) AS QtyStore,
               FROM tb_Products,
               tb_qtyStock,
               tb_StoreProdAddress 
             WHERE Product_Cod = wh_CodProduct
             AND Product_Cod = Store_CodProduct
             GROUP BY Product_Cod)
    ORDER BY Product_Cod

I hope it helps.

Dan · Accepted Answer · 2015-08-07 12:46:04Z

0

Simply remove the QtyStore column from your GROUP BY clause, and put SUM(QtyStore) in the SELECT-part of your query.

SELECT Product_Cod, qty_wh, SUM(QtyStore) AS TotalQtyStore,
    CASE
        WHEN sum(QtyStore) BETWEEN 1 AND 20000 THEN 
                                CASE
                                        WHEN qty_wh > 0 THEN 'In Stock'
                                        WHEN qty_wh = 0 THEN 'In Stock'
                                END
        WHEN sum(QtyStore) = 0 OR sum(QtyStore) > 20000 THEN
                                CASE
                                        WHEN qty_wh <= 0 THEN 'Out of Stock'
                                        WHEN qty_wh > 0 THEN 'in Stock'
                                END
    END AS [Result]
FROM tb_Products, tb_qtyStock, tb_StoreProdAddress
WHERE Product_Cod = wh_CodProduct
AND Product_Cod = Store_CodProduct
GROUP BY Product_Cod, qty_wh
ORDER BY Product_Cod

answered Aug 7, 2015 at 12:46

Dan

10.8k27 silver badges51 bronze badges

1 Comment

Alephtus Over a year ago

Thank you Dan! I'm pretty sure I tried that yesterday, but screwed it up somewhere because the actual query has way more to it, this is the short version of it, but now it is working like a charm! Blind spot, guess just needed another set of eyes to see my mistake XD

Collectives™ on Stack Overflow

SQL query using case and Group by aggregation

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related