CODEWITHSUNDEEP
mysqlsql
Petrosz
Petrosz

Reputation: 33

How can I select the minimum price per item from my database?

I want to select minimum price per item from my MySQL database, but I'm not sure how to write the query.

Here is an example table.

  id |  price | quantity
    1|   $200 |      200
    2|  $1000 |       10
    3|     $5 |        1

In this case the price/item for id 1 would be $1 (200/200), for id 2 it's $100 (1000/10), for id 3 it's $5 (5/1) and the query would return $1, because it is the minimum price/item.

So how can it be translated to SQL? I thought about: 

SELECT MIN(price)/200 WHERE quantity=200

but it only works with a hard coded quantity and I must write the quantity for each id and would be impossible with 1000+ different prices and quantities. So I would need something flexible with variables or something, like:

SELECT MIN(price)/quantity

but that's not correct syntax. (or it just doesn't work for me)

What is the correct way to write it in MySQL?

Upvotes: 2

Views: 1380

Answers (3)

Fabian Pijcke
Fabian Pijcke

Reputation: 3210

Here is a portable version of Gordon Linoff's solution :

SELECT t.*, (price / quantity) as unit_price
FROM  t t1
WHERE NOT EXISTS (SELECT * FROM t t2 WHERE t2.price / t2.quantity < t1.price / t1.quantity);

The point is that LIMIT is specific to some products (MySQL and PostgreSQL AFAIK).

Upvotes: 0

Manasa Chakka
Manasa Chakka

Reputation: 121

select min(Price/Quantity) from tbl

tbl is the table where you have your Price and quantity Data. This will work

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I think this does what you want:

select min(price / quantity)
from t;

If you want all the information in the row, then use order by and limit:

select t.*, (price / quantity) as unit_price
from t
order by unit_price asc
limit 1;

Upvotes: 4

Related Questions