CODEWITHSUNDEEP
mysqlsql
Geoff_S
Geoff_S

Reputation: 5107

Averaging database values from multiple columns, bypass zeroes/null

I'm trying to save an average based off of the values in several different columns, but some rows are null so they need to be replaced with zero. Also, at that point, I need the average to factor in the zeroes so that the example below would save the avg as "82.490". So it needs to replace null with zero, and then bypass any zeroes for the average.

1CorrectAcc  2CorrectAcc  3CorrectAcc  4CorrectAcc  5CorrectAcc  avg
   90.345        67.890       89.234       0              0

This is a pseudo query

UPDATE staging
SET `avg` = (1CorrectAcc + 2CorrectAcc + 3CorrectAcc + 4CorrectAcc + 5CorrectAcc) / COUNT avoiding zeroes

Upvotes: 0

Views: 40

Answers (2)

Strawberry
Strawberry

Reputation: 33935

Normalise your schema:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,account_id INT NOT NULL
,value DECIMAL(7,3)
);

INSERT INTO my_table VALUES
(1,1,90.345),
(2,1,67.890),
(3,1,89.234),
(4,1,NULL);

 SELECT * FROM my_table;
+----+------------+--------+
| id | account_id | value  |
+----+------------+--------+
|  1 |          1 | 90.345 |
|  2 |          1 | 67.890 |
|  3 |          1 | 89.234 |
|  4 |          1 |   NULL |
+----+------------+--------+

SELECT account_id, ROUND(AVG(value),3) FROM my_table GROUP BY account_id;
+------------+---------------------+
| account_id | ROUND(AVG(value),3) |
+------------+---------------------+
|          1 |              82.490 |
+------------+---------------------+

Upvotes: 1

Nahuel Fouilleul
Nahuel Fouilleul

Reputation: 19315

As the values are in column aggregation function can't be used

(
    coalesce(col1, 0) + 
    coalesce(col2, 0) +
    ...
) / (
    case when coalesce(col1, 0)=0 then 0 else 1 end +
    case when coalesce(col2, 0)=0 then 0 else 1 end +
    ...
)

If in a row all columns are 0 or null, a division by 0 error will be raised, it can be avoided adding following condition in where clause to filter these rows.

[where/and] not (
    coalesce(col1, 0) = 0 and
    coalesce(col2, 0) = 0 and
    ...
)

Upvotes: 1

Related Questions