CODEWITHSUNDEEP
sqlmysql
Sinan
Sinan

Reputation: 5980

how do I select AVG of multiple columns on a single row

How can I select average of multiple columns?

Suppose I have some data like: 

X   Y    Z
-------------
6   3    3
5   5    NULL
4   5    6
11  7    8

I want to get something like

AVG
-------------
4
5
5
8.66666667

I tried select avg(x, y, z) from table

But it doesn't work.

Any ideas on a query to do that?

Upvotes: 4

Views: 13381

Answers (3)

Larry Lustig
Larry Lustig

Reputation: 50970

You add them up, and divide by the number of values. The exclusion of NULLs from the denominator of the average operation is a little tricky:

SELECT (IFNULL(x, 0) + IFNULL(y, 0) + IFNULL(z, 0)) /
       (IIF(ISNULL(x), 0, 1) + IIF(ISNULL(y), 0, 1) + IIF(ISNULL(z), 0, 1))
  FROM YourTable

Upvotes: 3

Charles Bretana
Charles Bretana

Reputation: 146449

Try

 Select     (Coalesce(x,0) + Coalesce(y,0) + Coalesce(z,0)) /
       (Coalesce(x/x, 0) + Coalesce(y/y, 0) + Coalesce(z/z, 0))

or

 Select (Coalesce(x,0) + Coalesce(y,0) + Coalesce(z,0)) /
         (Case When x Is Null 0 Else 1 End +
          Case When y Is Null 0 Else 1 End +
          Case When z Is Null 0 Else 1 End)

Upvotes: 10

robbrit
robbrit

Reputation: 17960

Try:

(x + y + z) / 3

Upvotes: 1

Related Questions