Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
simondeutsch
Aged Yak Warrior
547 Posts |
 Posted - 2002-01-01 : 11:33:21
|
| Hiya!I'd like s/t like this:AVG(AmountPaid / SUM(CASE WHEN AmountPaid > 0 THEN Quantity ELSE 1 END)) AS AvgPaidPerPieceto calculate an average paid for a certain quantity of items, but only when there is a payment to the line having the the AmountPaid and Quantity fields. Thanks,Sarah |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-01-02 : 03:39:51
|
| select (sum(amountpaid)/sum( case amountpaid when 0 then 0 else quantity end))/count(amountpaid)/sum( case amountpaid when 0 then null else 1 end)from avepaidperpieceHTH----------------------------Anything that Doesn't Kills you Makes you Stronger |
 |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-01-02 : 12:29:42
|
| Thanks,Nazim,I figured out something else which worksSUM(CASE WHEN AmountPaid > 0 THEN AmountPaid / Qty ELSE 0 END) AS AvgPaidPerPiece.The SUM all around is b/c this is in a GROUP BY.Best Regards,Sarah |
|
|
|
|
|
|
Aggregate on Aggregate