?
Solved

SQL Statement help

Posted on 2004-08-10
4
Medium Priority
?
335 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Progress
Suggested Courses

800 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