Question 112810 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – The minimum value from each category

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

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search