How to select Min and Max price from category base on regular or promo price-CodePudding

I like to select MIN and MAX price from category but if products have promo price should select promo price is not should select regular price

table look like this

CREATE TABLE `products` (
`id` int(11) NOT NULL,
`name` varchar(155) NOT NULL,
`category_id` varchar(25) NOT NULL,
`cost` decimal(65,2) NOT NULL,
`promo_cost` decimal(62,2) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

This query work only if in category all products have promo price or do not have .

How to make it work for category where are mixed products with promo and without promo ?

SELECT 
 CASE 
  WHEN promo_cost != 0 
  THEN MAX(promo_cost) 
  ELSE MAX(cost) END as max_price 
FROM products WHERE category_id = $category_id

for example if i have in category:

product 1: cost 50.00, promo_cost 30.00

product 2: cost 60.00, promo_cost 40.00

product 3: cost 20.00, promo_cost 0.00

max should be 40 and min should be 20

CodePudding user response：

I think this is your variant

select min(tmpPrice) as min_price,max(tmpPrice) as max_price from
    (
        SELECT CASE WHEN promo_cost != 0 THEN promo_cost ELSE cost END as tmpPrice
            FROM products WHERE category_id = $category_id
    ) a

CodePudding user response：

Based on your expected output, I think you want:

SELECT MIN(cost) AS min_cost, MAX(promo_cost) AS max_cost
FROM products
WHERE category_id = ?;

That is, the minimum cost is the smallest cost, and the maximum cost is the largest promo_cost.