postgresql coalesce

Question

Can anyone say why this select returns 3.0 instead of 3.5:

SELECT coalesce(1.0*(7/2),0) as foo

This one returns 3:

SELECT coalesce(7/2,0) as foo

I'm just trying to divide a SUM by COUNT to find an average. I need it 0 if null and 1 rounded by 1 decimal in case I have results.

Answer 1

You need to do something like:

SELECT COALESCE((1.0 * 7)/2, 0) AS foo;

Answer 2

Well, 7/2 is an integer, 1.0 is numeric, and so 1.0*(7/2)==1.0 * 3 == 3.0.