Merve Bozo Merve Bozo - 2 months ago 6
SQL Question

Put average of column instead of 0 values mysql select query

I am trying to select a table column by
putting the average of that column if any of the values is 0.

My query:

SELECT IF(my_column= 0, AVG(number), number) FROM table;

The problem is with this query I only get the 1 row; when I put a number instead of AVG(number) like

SELECT IF(my_column= 0, 100, number) FROM table;

Everything is good.

So, how can I put the average value instead of 0's while selecting a column.

Thank you,


You can cross-join with a subquery that selects the average and then use that:

SELECT IF(t.my_column=0, a.avg, t.number)
FROM   table t, (SELECT AVG(number) AS avg FROM table) a