×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Trying to get MIN MAX with CASE statement

Trying to get MIN MAX with CASE statement

Trying to get MIN MAX with CASE statement

(OP)
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)

RE: Trying to get MIN MAX with CASE statement

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

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

RE: Trying to get MIN MAX with CASE statement

(OP)
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!

RE: Trying to get MIN MAX with CASE statement

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...):
COALESCE(MIN(case when TransactionType = "Buy" then Price end),0)


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

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

RE: Trying to get MIN MAX with CASE statement

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close