Menu
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?
608
One approach is AVG() and CASE / NULLIF() : SELECT AVG(NULLIF(Column1, 0)) as Average FROM table1; Average ignores NULL values.
To exclude entries with “0”, you need to use NULLIF() with function AVG().
The avg() function has the following syntax: SELECT AVG( column_name ) FROM table_name; The avg() function can be used with the SELECT query for retrieving data from a table.
This query throws an error, because you cannot use AVG() in a WHERE condition. Since AVG() is a group function, you can only use it in a SELECT statement or in a HAVING clause. The query inside a main query is called a subquery.
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))
, ( SELECT AVG(a) FROM
(
SELECT NULLIF([Column1], 0)
UNION ALL
SELECT NULLIF([Column2], 0)
UNION ALL
SELECT NULLIF([Column3], 0)
UNION ALL
SELECT NULLIF([Column4], 0)
) T (a)
) AS [4 Column Average]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
