TSQL - Average of all values in a column that are not zero

tsql average
Justin808 picture Justin808 · Jan 20, 2011 · Viewed 19.5k times · Source

I'm in the process of writing a report and am looking to get the average value of an age column. The problem is that not all rows have an age.

If the values for the column are 0 2 4 I would want 3 returned, not 2. I can not simply exclude the zero rows with a WHERE as I'm using using other columns in those rows. Is there such a thing as a AvgIfNotZero type of function?

Answer

gbn picture gbn · Jan 20, 2011 
SELECT

    AVG (CASE WHEN Value <> 0 THEN Value ELSE NULL END)
    ....

AVG won't take into account NULL values. Or this

    AVG (NULLIF(Value, 0))

Related questions

Avg of a Sum in one query

I would like to know if I can get the average of a sum in one single SQL SERVER request, Have tried to do it with the following request but it doesn't work: SELECT t.client, AVG(SUM(t.asset)) …

Read More
Take Hourly Average in SQL

I have a SQL table with 2 fields: TimeStamp and Value. Below is an excerpt of some of the data. 2005-02-17 13:31:00 2 2005-02-17 13:46:00 3 2005-02-17 14:01:00 1.7 2005-02-17 14:16:00 2.3 2005-02-17 14:31:00 2 2005-02-17 14:46:00 2.5 2005-02-17 15:01:00 2.2 2005-02-17 15:16:00 2.4 2005-02-17 15:31:00 2.6 2005-02-17 15:46:00 2.6 2005-02…

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More