• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • 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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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