CODEWITHSUNDEEP
mysql
gtviga
gtviga

Reputation: 91

Average of multiple column with no data involved

I have this data from the table score below:

No   !  id   !  A      !  B    ! average  !
------------------------------------------
1    !  01   !  80     !  60   !    70    !
2    !  02   !         !  60   !    30    !
3    !  03   !  0      !  60   !    30    !
4    !  04   !  80     !       !    40    !

I want to get the average of column A and B. using code below:

Select id, (avg(A)+avg(B))/2 as Average
from score
Group by id;

the problem is, id 02 and id 04 have only one data. The average will count it as 0 instead of no data.

table that I want is like this I have this data from the table score below:

No   !  id   !  A      !  B    ! average  !
------------------------------------------
1    !  01   !  80     !  60   !    70    !
2    !  02   !         !  60   !    60    !
3    !  03   !  0      !  60   !    30    !
4    !  04   !  80     !       !    80    !

anybody can help me?

Upvotes: 3

Views: 56

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

Try this query:

SELECT id, (SUM(COALESCE(A,0)) + SUM(COALESCE(B,0))) / (COUNT(A) + COUNT(B))
FROM score
GROUP by id

The trick here is to compute the average in pieces, by taking the SUM divided by the COUNT, and using COALESCE() appropriately to handle NULL values. Keep in mind that the MySQL aggregate functions (SUM, COUNT, etc.) ignore NULL values by default.

I tested the query and you can add additional columns beyond A and B and it still seems to be working.

Demo:

SQLFiddle

Upvotes: 3

Bruce David Wilner
Bruce David Wilner

Reputation: 467

You can't have floating-point averages in an integer field. Unfortunately, averaging will give integer results, so an average of, say, 0 and 1 will be 0. That having been said, your "average" is guaranteed to be at least as large as the MIN() value in the column, and no larger than the MAX() value in the column. If you will want floating-point numbers to be reported in the same column as integers, then don't make it an integer column!

Upvotes: 0

Related Questions