Issues with MIN, MAX and Case statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Issues with MIN, MAX and Case statement

Author

Topic

faimuj
Starting Member

9 Posts

Posted - 2012-01-24 : 00:28:26

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)

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-01-24 : 00:47:03

It's because you are not checking your condition correctly. If you want to exclude a min transaction you need to make the number huge, not zero!
MIN(case when TransactionType = "Buy" then Price else ReallyBigNumber ) as MinBuy,
MIN(case when TransactionType = "Sell" then Price else ReallyBigNumber ) as MinSell

faimuj
Starting Member

9 Posts

Posted - 2012-01-24 : 09:49:43

Actually, I don't want to exclude the MIN transactions. I want to get the min transactions. I tried to put a very large number on the else side and it just returned the very large number instead of the zero for the MIN prices. That would not work as well.

What I am trying to do is to get the MIN and MAX prices for a grouping of AccountNumber & Symbol. However, when there are multiple transaction types for a particular grouping, like 123 and "XYZ", I am not able to get the MIN prices. The MAX prices come out fine.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2012-01-24 : 11:18:07

You will just have to live the the NULL value eliminated warnings or have another CASE to detect the very large number.


SELECT AccountNumber, Symbol
	,COALESCE(SUM(CASE WHEN TransactionType = 'Buy' THEN 1 END), 0) AS TotalBuys
	,COALESCE(SUM(case WHEN TransactionType = 'Sell' THEN 1 END), 0) AS TotalSells
	,COALESCE(MIN(case WHEN TransactionType = 'Buy' THEN Price END), 0) AS MinBuy
	,COALESCE(MAX(case WHEN TransactionType = 'Buy' THEN Price END), 0) AS MaxBuy
	,COALESCE(MIN(case WHEN TransactionType = 'Sell' THEN Price END), 0) AS MinSell
	,COALESCE(MAX(case WHEN TransactionType = 'Sell' THEN Price END), 0) AS MaxSell
	,MIN(Price) AS MinPrice
	,MAX(Price) AS MaxPrice
FROM Trades
GROUP BY AccountNumber, Symbol

faimuj
Starting Member

9 Posts

Posted - 2012-01-24 : 11:47:38

Ifor, thank you for you assistance! That worked great!

Ifor
Aged Yak Warrior

700 Posts

Posted - 2012-01-24 : 12:01:06

If you do not want the NULL warnings, try something like:


WITH TradePivot
AS
(
	SELECT AccountNumber, Symbol
		,SUM(CASE WHEN TransactionType = 'Buy' THEN 1 ELSE 0 END) AS TotalBuys
		,SUM(CASE WHEN TransactionType = 'Sell' THEN 1 ELSE 0 END) AS TotalSells
		,MIN(CASE WHEN TransactionType = 'Buy' THEN Price ELSE 999999 END) AS MinBuy
		,MAX(CASE WHEN TransactionType = 'Buy' THEN Price ELSE 0 END) AS MaxBuy
		,MIN(CASE WHEN TransactionType = 'Sell' THEN Price ELSE 999999 END) AS MinSell
		,MAX(CASE WHEN TransactionType = 'Sell' THEN Price ELSE 0 END) AS MaxSell
		,MIN(Price) AS MinPrice
		,MAX(Price) AS MaxPrice
	FROM #t
	GROUP BY AccountNumber, Symbol
)
SELECT AccountNumber, Symbol, TotalBuys, TotalSells
	,CASE WHEN MinBuy = 999999 THEN 0 ELSE MinBuy END AS MinBuy
	,MaxBuy
	,CASE WHEN MinSell = 999999 THEN 0 ELSE MinSell END AS MinSell
	,MaxSell, MinPrice, MaxPrice
FROM TradePivot;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources