PostgreSQL query to check minimum and maximum data

Question

i have the example database here,

table products

id |  name  |  code  |  minimum_stock  |  stock  |  maximum_stock  
1     AAA      AAA            50           75           100
2     BBB      BBB            70           50           300
3     CCC      CCC            100          200          150
4     DDD      DDD            40           25           100
5     EEE      EEE            70           10           100

in this case i want to show only data who is stock exceed compared by their minimum_stock or maximum_stock(below or above their minimum maximum stock) and also i want to show datain safe stock position (between minimum and maximum stock), stock value in here is a result from aggregate sum function

if i want only show data exceed minimum stock, the result output must be like this

id |  name  |  code  |  **minimum_stock**  |  **stock**  |  maximum_stock  
2     BBB      BBB            **70**           **50**           300
4     DDD      DDD            **40**           **25**           100
5     EEE      EEE            **70**           **10**           100

and if i want only show data exceed maximum stock, the result output must be like this

id |  name  |  code  |  minimum_stock  |  **stock**  |  **maximum_stock**  
3     CCC      CCC            100          **200**          **150**

and the last one the stock between minimum and maximum stock, it must be like this

id |  name  |  code  |  **minimum_stock**  |  **stock**  |  **maximum_stock**  
1     AAA      AAA            **50**           **75**           **100**

how can I do that?

do you want three queries or one? are the ** part of the requred output or just for emphasis? — Jasen
– Jasen, Commented Jan 14, 2015 at 6:08

SimarjeetSingh Panghlia · Accepted Answer · 2015-01-14 05:33:21Z

1

Try this

Minimum Stock

Select * from table where stock < minimum_stock

maximum stock

Select * from table where stock > maximum_stock

between minimum and maximum stock

Select * from table where stock < maximum_stock and stock > minimum_stock

answered Jan 14, 2015 at 5:33

SimarjeetSingh Panghlia

2,21016 silver badges14 bronze badges

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

4 Comments

azy Over a year ago

how about the stock is a result from sum operation, i tried it but its error

azy Over a year ago

i got this message ERROR: aggregates not allowed in WHERE clause

Jasen Over a year ago

@azy use HAVING instead of WHERE if there are agregates in the condition

SimarjeetSingh Panghlia Over a year ago

check out this link :stackoverflow.com/questions/20991729/…

Collectives™ on Stack Overflow

PostgreSQL query to check minimum and maximum data

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related