CODEWITHSUNDEEP
mysqlsqlgroup-by
SoluriX
SoluriX

Reputation: 57

MySQL: Filter GROUP BY result

I have a table of children that have different price values. I need to find values between different ranges.

SELECT parent_id,
    MIN(price) as min_price, -- does what I want
    MIN(price WHERE price > 50) as p_50, -- I wish I could do sth like this
    MIN(price WHERE price > 100) as p_100 -- There would be more lines like this
FROM price_table
GROUP BY parent_id;

I know that

MIN(price WHERE price > 50)

Is wrong but it represents what I want to achieve - find the lowest price greater than 50. I could make an additional SELECT statement for that but I need to find prices for more ranges and having 20 select statements seems very inefficient.

Is there a way to efficiently query the list of prices in this grouped statement or do I need to create multiple JOINs that differ only by the price value?

Upvotes: 1

Views: 222

Answers (2)

Chris Maurer
Chris Maurer

Reputation: 2625

The formula you're looking for is a Case statement:

min(Case When price>50 Then price End)

Upvotes: 3

kurniadi445
kurniadi445

Reputation: 142

You can use having to filter aggregate function.

select parent_id,
       (select min(price) from price_table b where b.parent_id = a.parent_id) min_price,
       (select min(price) from price_table c where c.parent_id = a.parent_id having min(price) > 50) p_50,
       (select min(price) from price_table d where d.parent_id = a.parent_id having min(price) > 100) p_100
from price_table a

From MySQL reference:

The HAVING clause, like the WHERE clause, specifies selection conditions. The WHERE clause specifies conditions on columns in the select list, but cannot refer to aggregate functions. The HAVING clause specifies conditions on groups, typically formed by the GROUP BY clause. The query result includes only groups satisfying the HAVING conditions. (If no GROUP BY is present, all rows implicitly form a single aggregate group.)

Upvotes: 1

Related Questions