CODEWITHSUNDEEP
sqlsql-servert-sql
jjjjjjjjjjj
jjjjjjjjjjj

Reputation: 447

Get average in SQL ignoring all values less than 0

How do you get the average of a column while ignoring anything less than zero, without using @TempTables? I tried this, thinking it would only set the less-than-zero values to 0, but instead it sets everything to 0 if even one value is <0.

[AverageCost]=avg(case when [Cost]>0 then [Cost] else 0 end)

Edit: To clarify, I already tried excluding them in a WHERE clause, but some columns have positive and negative values. This made the query exclude columns with an overall negative average, when it should only reject negative values when doing the average. So if a column has values of -20, -5, 5, 10, 15, then the average would be 10.

Upvotes: 2

Views: 6084

Answers (2)

Donal
Donal

Reputation: 32713

SELECT 
[AverageCost] = AVG(cost)
FROM tablename
WHERE
cost > 0

Upvotes: 7

Dan Field
Dan Field

Reputation: 21641

Assuming you can limit your entire query (Which isn't entirely clear yet), you could do this:

DECLARE @AverageCost int;
SELECT @AverageCost = AVG(Cost)
FROM TableName
WHERE Cost > 0

Your answer is probably going to look somethign like that regardless - for example, set the variable first and then run your whole query:

UPDATE SomeOtherTable
SET AverageCost = @AverageCost, AnotherField = FieldB
FROM TableName
-- now we don't have to exclude negative values.

Alternatively, you could keep something close to what you had:

SELECT AVG(CASE WHEN Cost > 0 THEN Cost ELSE NULL END)
FROM TableName

This will have a similar effect without limiting your entire query - but note the difference, NULL instead of 0 (think about the difference between averaging in a 0 vs skipping a value).

Upvotes: 6

Related Questions