Deadpool Deadpool - 1 month ago 5
SQL Question

Sql Query To Return Notification from store table

I have a

store
table which have
productName
,
productNumber
,
storedInBranch
,
blockNumber
,
quantity
,
notifyIn
.

If the
quantity
is less than
notifyIn
it will notify me and I tried the query below:

select * from store
where (quantity < notifyIn) AND (notify > 0)


which works perfect but since one product can be stored in multiple
blockNumbers
it is notifying me even if the amount of products are not less than
notifyin
,

eg.:

productName = monitor, productNumber=123, storedInBranch=kenya, blocknumber=5b, quantity=5, notifyin=4

productName = monitor, productNumber=123, storedInBranch=kenya, blocknumber=<b>1a</b>, quantity=5, notifyin=4


so the above query returns both as low what I wanted to do is sum both
quantity
(5+5) and
notifyIn
4.

Answer

You are relying here on notifyin being the same value in multiple rows. You could do something like

SELECT productNumber, sum(quantity), min(notifyin)
FROM store
GROUP BY productNumber
HAVING sum(quantity) < min(notifyin)

I would encourage you to change your schema if possible to better reflect your domain logic. Maybe have a separate table with product number and notify in value. That way you are not duplicating the notifyin and risk storing different value in different rows.

Comments