Yanick - 1 year ago 29

SQL Question

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.

`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)
```