Yanick Yanick - 7 months ago 7
SQL Question

MySQL - Query with grouped conditions

enter image description here

Hi,

I have table with row like the above picture and i would like to sum QTY of all row but i need to exclude row where AISLE = POS and QTY < 0 on the same row.

I made some try to get what i want but i can't find solution :

SELECT
ROUND(SUM(QTY),
2) AS INVENTORY
FROM
INV_QTY_LOCATION
WHERE
PRODUCT = 143459 AND AISLE != 'PHY' AND AISLE != 'RET' AND case when AISLE = 'POS' AND QTY > 0


Another try

SELECT
ROUND(SUM(QTY),
2) AS INVENTORY
FROM
INV_QTY_LOCATION
WHERE
PRODUCT = 143459 AND AISLE != 'PHY' AND AISLE != 'RET' AND (AISLE = 'POS' AND QTY > 0)


In this particular case the result should be 161.

Solution

SELECT
ROUND(
SUM(
CASE WHEN(AISLE = 'POS' AND QTY > 0) OR AISLE != 'POS' THEN QTY ELSE 0
END
),
2
) AS INVENTORY
FROM
INV_QTY_LOCATION
WHERE
PRODUCT = 143459 AND AISLE != 'PHY' AND AISLE != 'RET'

Answer

I want to note that NOT IN would be helpful for this query:

WHERE PRODUCT = 143459 AND
      AISLE NOT IN ('PHY', 'RET') AND
      (AISLE <> 'POS' OR QTY > 0)

Note: This assumes that AISLE is never NULL.

Or, alternatively:

WHERE PRODUCT = 143459 AND
      AISLE NOT IN ('PHY', 'RET') AND
      NOT (AISLE = 'POS' AND QTY > 0)
Comments