• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

SQL Statement help

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
tw_chase
Asked:
tw_chase
  • 2
1 Solution
 
Jaime OlivaresSoftware ArchitectCommented:
Why not to use where clause?
Avg(Amt) as 'TestAmt' where Bids<>0
0
 
tw_chaseAuthor Commented:
Doesn't work in SQL Server 2000.  Thanks for the try though
0
 
brandonh6kCommented:
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
 
tw_chaseAuthor Commented:
Brandon,

Worked great!

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now