The minimum value from each category-CodePudding

I want to get out the minimum price for categories 1, 2 and 3 I've used

   LEAST(MIN(price_reduced),MIN(price))
   IFNULL(MIN(price_reduced),MIN(price)) ... WHERE price <> 0 and price_reduced <> 0

Database

id	category	price	price_reduced
1	1	200	100
2	1	300	0
3	1	500	0
4	2	200	150
5	2	125	0
6	3	300	0
7	3	200	90

Output

1 - 100
2 - 125
3 - 90

Thank you

CodePudding user response：

Maybe with cte:

WITH cte AS (SELECT category,
       MIN(price) AS mp,
       MIN(CASE WHEN price_reduced <= 0 THEN 9999 ELSE price_reduced END) pr
  FROM mytable 
GROUP BY category)
SELECT category, LEAST(mp, pr) AS min_val
  FROM cte;

Or without cte but with derived table:

SELECT category, LEAST(mp, pr) AS min_val
  FROM (SELECT category,
       MIN(price) AS mp,
       MIN(CASE WHEN price_reduced <= 0 THEN 9999 ELSE price_reduced END) pr
  FROM mytable 
GROUP BY category) a;

Or just a single query:

SELECT category,
       LEAST(MIN(price),
       MIN(CASE WHEN price_reduced <= 0 THEN 9999 ELSE price_reduced END)) AS min_val
  FROM mytable 
GROUP BY category;

All return the same results.

Demo fiddle

CodePudding user response：

This query will work on all MySQL V4 :

SELECT Category, 
MIN(IF((price_reduced > 0) AND (price_reduced < price), price_reduced, price)) AS P 
FROM your_table GROUP BY Category;