Trying to get MIN MAX with CASE statement 1

[faimuj]

Hello,

I am trying to generate a summary report using various aggregate functions: MIN, MAX, SUM, etc. The issue I have is when I try to get a MIN and MAX of a field when I am also using the case statement. I am unable to get the MIN value of a field when I am using the case statement. I can best explain it with sample data and the sql statement:

Fields: AccountNumber, Symbol, TradeDate, TransactionType, Price, Quantity, Amount
Table: Trades

AccountNumber, Symbol, TradeDate, TransactionType, Price, Quantity, Amount
123,"XYZ",1/2/2011,"Buy",15,100,1500
123,"XYZ",1/2/2011,"Buy",10,50,500
123,"XYZ",1/2/2011,"Sell",20,100,2000
456,"ABC",1/3/2011,"Buy",10,20,200
456,"ABC",1/3/2011,"Buy",15,30,450
789,"DEF",1/4/2011,"Sell",30,100,3000

SELECT
AccountNumber,
Symbol,
SUM(case when TransactionType = "Buy" then 1 else 0) as TotalBuys,
SUM(case when TransactionType = "Sell" then 1 else 0) as TotalSells,
MIN(case when TransactionType = "Buy" then Price else 0) as MinBuy,
MAX(case when TransactionType = "Buy" then Price else 0) as MaxBuy,
MIN(case when TransactionType = "Sell" then Price else 0) as MinSell,
MAX(case when TransactionType = "Sell" then Price else 0) as MaxSell,
MIN(Price) as MinPrice,
MAX(Price) as MaxPrice
FROM Trades
Group By AccountNumber, Symbol

What I am expecting is the following results:

AccountNumber, Symbol, TotalBuys, TotalSells, MinBuy, MaxBuy, MinSell, MaxSell, MinPrice, MaxPrice
123,"XYZ",2,1,10,15,20,20,10,20
456,"ABC",2,0,10,15,0,0,10,15
789,"DEF",0,1,0,0,30,30,30,30

However, I am getting the following results:

AccountNumber, Symbol, TotalBuys, TotalSells, MinBuy, MaxBuy, MinSell, MaxSell, MinPrice, MaxPrice
123,"XYZ",2,1,0,15,0,20,0,20
456,"ABC",2,0,10,15,0,0,10,15
789,"DEF",0,1,0,0,30,30,30,30

When there are two different TransactionTypes for each grouping, the Min fields (MinBuy,MinSell, and MinPrice) are coming out as 0 as opposed to what is expected. What am I doing wrong on the sql statement? Is there another way to get the desired results?

Thank you in advance,

Desperately Seeking SQL (Answers)

 

[JarlH]

The [tt]MIN(case when TransactionType = "Buy" then Price else 0)[/tt] will return 0 when the transaction type is sell.

Either remove the "else 0" part, or replace 0 with MAXINT.

 

[faimuj]

JarlH, thank you for replying. I tried both ways; however, it id not produce the desired results. If I remove the "else 0," the results returns null not the expected values. Also, I tried "MAXINT." Although, in Sybase (which I am using), does not support MAXINT. Any other thoughts is greatly appreciated.

Thanks!

 

[JarlH]

Sorry, I didn't give you the complete solutions, just hints.

If you don't like NULL's, it can be solved with COALESCE (which is ANSI SQL, I don't know if Sybase has the same syntax...):
[tt]COALESCE(MIN(case when TransactionType = "Buy" then Price end),0)[/tt]


When I wrote MAXINT I meant a very large integer value, one way larger than any possible price, e.g.
[tt]MIN(case when TransactionType = "Buy" then Price else 100000000 end)[/tt]

Personally I prefer the first solution, the second one isn't very beautiful.

 

[faimuj]

JarlH, thank you very much! The first method worked beautifully!

Although, when I tried the second method, it just returned the very large number and did not provide the expected results. Any how, I am grateful for your assistance on resolving this issue.

Thanks.