Menu
I need to perform an avg on a column, but I know that most of the values in that column will be zero. Out of all possible rows, only two will probably have positive values. How can I tell mySQL to ignore the zeros and only average the actual values?
631
SQL Average function and NULL valuesAVG() function does not consider the NULL values during its calculation.
MySQL AVG function is used to find out the average of a field in various records. You can take average of various records set using GROUP BY clause. Following example will take average all the records related to a single person and you will have average typed pages by every person.
Example #2: How Does the AVG() Function Deal With NULL Values in Postgres? The output shows that the AVG() ignores the NULL values.
Assuming that you might want to not totally exclude such rows (perhaps they have values in other columns you want to aggregate)
SELECT AVG(NULLIF(field ,0))
from table
You could probably control that via the WHERE clause:
select avg( field ) from table where field > 0
select avg(your_column)
from your_table
where your_column != 0
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
