Clever Geek Handbook

MySQL AVG error with ELSE 0

I am doing the average in a column based on the values ​​of the other two columns.

The table structure is like

| rid | rate | status |....
|  1  | 1500 |   1    |....
|  1  | 1500 |   1    |....
|  1  | 1500 |   1    |....
|  1  | 1500 |   1    |....
|  2  | 1500 |   1    |....

When I do this, it gives the correct result 1500

, but it returns NULL

for a match CASE

, I need to AVG

return 0

if the rows do not match CASE

.

AVG(CASE WHEN `rid` = `status` THEN `rate` END) DIV 1 AS `avg`

I tried this way, but it gave me the wrong result: 1200 

AVG(CASE WHEN `rid` = `status` THEN `rate` ELSE 0 END) DIV 1 AS `avg`

Why am I getting the wrong result?

Please take a look and suggest any possible way to do this.

thank

+3


source to share


5 answers


try it

IFNULL(AVG(CASE WHEN `rid` = `status` THEN `rate` END) DIV 1,0) AS `avg`


Note: MySQL IFNULL takes two arguments, if the first arguments are non-zero then it returns the first arguments, otherwise it returns the second argument

+3


source 


SELECT AVG(RATE) FROM TABLENAME GROUP BY rid,status

Try to execute the request.



Hope this helps you.

+1


source


You get 1200 because the last line where the register returns 0 will still count as a string, so (1500 * 4 + 0) / 5 = 1200 .

+1


source


The reason is based on the filter filtering rows from the table In the first select

  AVG(CASE WHEN `rid` = `status` THEN `rate` END) DIV 1 AS `avg`

condition in case when only 4 lines are executed
do what you haven't set yet, that the query is a filter only for matching rid

=status

In the second

AVG(CASE WHEN `rid` = `status` THEN `rate` ELSE 0 END) DIV 1 AS `avg`

the else clause implies using the last line (rid = 2), so you have 5 lines with 4 valid contents for avg

+1


source


Apply COALESCE

to your result:

COALESCE(AVG(CASE WHEN `rid` = `status` THEN `rate` END), 0) DIV 1 AS `avg`
0


source






More articles:


All Articles