CODEWITHSUNDEEP
sql-server
enharmonic
enharmonic

Reputation: 2080

SQL multiple select with multiple conditions

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?

Upvotes: 0

Views: 237

Answers (1)

slayer3600
slayer3600

Reputation: 323

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)

Upvotes: 2

Related Questions