Search code examples
sqldatabasesql-server-2012-express

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.

Solution

  • 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.