Merve Bozo Merve Bozo - 1 year ago 85
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,

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download