SQL multiple select with multiple conditions

Question

I have a data table "BondPrices" with the following values:

date     | type | price  
1/5/2019   bid    104.50  
1/5/2019   bid    104.75
1/6/2019   bid    104.40  
1/6/2019   bid    104.45   
1/5/2019   offer  106.75   
1/5/2019   offer  107.75
1/6/2019   offer  106.23
1/6/2019   offer  106.47

I want to write a query that gives the highest price for bids and the lowest price for offers on a given date, like the following output:

date     | Highest Bid | Lowest Offer
1/5/2019   104.75        106.75
1/6/2019   104.45        106.23

I can achieve either highest bid or lowest offer by doing

SELECT "date", max("price") AS "Highest Bid"
FROM "BondPrices" 
WHERE "type"='bid' 
GROUP BY "date"

or

SELECT "date", min("price") AS "Lowest Offer"
FROM "BondPrices" 
WHERE "type"='offer' 
GROUP BY "date"

but when I try to combine them (such as with JOIN) I get a syntax error.

How do I write a select statement with multiple conditions resulting in multiple columns?

slayer3600 · Accepted Answer · 2019-03-08 02:51:40Z

2

There's a few ways to skin the cat here, this will get you there:

SELECT
    CAST(date AS DATE) Date,
    MAX(CASE WHEN type = 'bid' THEN price ELSE NULL END) HighestBid,
    MIN(CASE WHEN type = 'offer' THEN price ELSE NULL END) LowestOffer
FROM 
    BondPrices
GROUP BY 
    CAST(date AS DATE)

answered Mar 8, 2019 at 2:51

slayer3600

3231 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL multiple select with multiple conditions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related