Solved

SQL Statement help

Posted on 2004-08-10
4
286 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
  • 2
4 Comments
 
LVL 55

Expert Comment

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

Author Comment

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

Accepted Solution

by:
brandonh6k earned 500 total points
Comment Utility
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
Comment Utility
Brandon,

Worked great!

Thanks
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
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 …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now