CODEWITHSUNDEEP
mysqlsql
D.Terelia
D.Terelia

Reputation: 13

min and max by different conditions in one select

I have a list of offers and requests of goods in 1 table, and i need to find the max priced requests and min priced offers grouped by goods

There is not a big problem to get all requests with max price and all offers with min price, but i need to get offers min price for each request.

My table looks like

ItemName |  Type   | ItemPrice
-----------------------
item1    | offer   | 100
item1    | offer   | 80
item1    | request | 120
item2    | offer   | 50
item2    | request | 30
item2    | request | 60

And as result i need

ItemName | OfferMinPrice | RequestMaxPrice
-----------------------
item1    |      80       |      120
item2    |      50       |       60

Upvotes: 1

Views: 98

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can just use conditional aggregation:

select itemname,
       min(case when type = 'offer' then itemprice end) as min_offerprice,
       max(case when type = 'request' then itemprice end) as max_requestprice,
from t
group by itemname;

Upvotes: 2

Related Questions