SQL - condition on field value?

Question

Hi, I would like to know whether I can test some value against a condition in each returned row somehow in the query?

e.g.: I have columns:

X   Y   Z
-1   1  2
2   2   -1
3   -1  3

I want to use avg() for all values except for -1. I CANNOT use where<> -1 as each row contains it once.

Answer 1

Use WHERE to filter away the values you don't want to include in your average, for example to include all numbers in the average except for -1:

SELECT
    (SELECT AVG(x) FROM table1 WHERE x >= 0) AS x,
    (SELECT AVG(y) FROM table1 WHERE y >= 0) AS y,
    (SELECT AVG(z) FROM table1 WHERE z >= 0) AS z

Note that if you really want to include all numbers except -1 as you said in your question then you should change the WHERE clause to x <> -1 but I doubt that this is what you want.

Answer 2

Try the following:

SELECT AVG(IF(x <> -1, x, NULL)) AS avgX,
       AVG(IF(y <> -1, y, NULL)) AS avgY,
       AVG(IF(z <> -1, x, NULL)) AS avgZ
FROM mytable;