Issues with MIN & MAX when using Case statement

Question

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

Query:

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?

Answer 1

Min between 0 and a positive number is 0, you should change:

MIN(case when TransactionType = "Buy" then Price else 0)

by

MIN(case when TransactionType = "Buy" then Price else Null)

Null don't compute in an aggregation function.

Thats all.

Edited 6 years later:

As P5Coder says, it is enough without else clause, also I guess the end is mandatory on some database brands. Here it is:

MIN(case when TransactionType = "Buy" then Price end)