[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Statement help

Posted on 2004-08-10
4
Medium Priority
?
340 Views
Last Modified: 2010-04-17
Please review this previous question for further info:
http://www.experts-exchange.com/Programming/Q_21071172.html

That worked great.  The problem now is some of my results are a little off.  The AvgAmt, MaxAmt, and MinAmt are off.  They should not include in the records in the average that the "Num" field is 0.  Currently they do.  

I tried adding this to the statement for the AvgAmt field but it is not right even though it executes fine:

Avg(Case WHEN Bids > 0 THEN Amt Else 0 End) as 'TestAmt'

I get $17.59 when it should be $138.37

PLEASE HELP!!!  Thanks in advance.


0
Comment
Question by:tw_chase
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 55

Expert Comment

by:Jaime Olivares
ID: 11764883
Why not to use where clause?
Avg(Amt) as 'TestAmt' where Bids<>0
0
 

Author Comment

by:tw_chase
ID: 11764930
Doesn't work in SQL Server 2000.  Thanks for the try though
0
 
LVL 3

Accepted Solution

by:
brandonh6k earned 2000 total points
ID: 11766110
Replace your zero with a null.  Average doesn't count null values.

So you should have this...

Avg(Case WHEN Bids > 0 THEN Amt Else null End) as 'TestAmt'

Same thing applies to the Min and Max functions.

HTH,

Brandon
0
 

Author Comment

by:tw_chase
ID: 11766722
Brandon,

Worked great!

Thanks
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
Progress
Introduction to Processes

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question