CODEWITHSUNDEEP
mysqlmin
user4383535
user4383535

Reputation:

how to use mysql function "min()" with two criteria in one query

I have a table of items some are second hand and some are new,

I would like a query giving me the minimum price for the new version of the item as well as the second hand version.

I have a boolean field called "new" who tell if the item is second hand or not

for the result I have to do two query but I think its possible to do only one

query for the second hand :

SELECT  ref_product, MIN( price ) 
FROM item_list
WHERE new = 0
GROUP BY ref_produit

query for the new :

SELECT  ref_product, MIN( price ) 
FROM item_list
WHERE new = 1
GROUP BY ref_produit

I would like that kind of result :

ref_product | min_price_for _secondhand | min_price_for_new

Upvotes: 0

Views: 170

Answers (3)

juergen d
juergen d

Reputation: 204766

SELECT  ref_product, 
        MIN(case when new = 1 then price else null end) as new_price,
        MIN(case when new = 0 then price else null end) as used_price
FROM item_list
GROUP BY ref_product

Upvotes: 1

axiac
axiac

Reputation: 72226

Drop the condition on new (get all products) and group by ref_produit' then bynew`:

SELECT ref_product, new, MIN(price) AS min_price
FROM item_list
GROUP BY ref_produit, new

Upvotes: 0

SMA
SMA

Reputation: 37023

Try something like:

SELECT ref_product, MIN( price ) 
FROM item_list 
WHERE new in (0,1)
GROUP BY new, ref_product

Upvotes: 0

Related Questions