null value in queries to default in zero
i have a table test1 as create test1(f1 integer, f2 integer);
select * from test1 will return
f1 | f2
--+--
1 | 2
2 | 3
3 | 4
4 | 5
5 | 6
(5 rows)
select to_char(avg(f1),'FM999999.99') from test will yield
to_char
--------
3
(1 row)
select to_char(avg(f1),'FM999999.99') from test where f2 <= 5 will yield
to_char
--------
2.5
(1 row)
select to_char(avg(f1),'FM999999.99') from test where f2 > 7 will yield
to_char
--------
(1 row)
is their a way to make the value zero if the return is null?
On Tue, 2002-11-12 at 07:49, Dorward Villaruz wrote:
select to_char(avg(f1),'FM999999.99') from test where f2 > 7 will yield
to_char
--------(1 row)
is their a way to make the value zero if the return is null?
Sure. The easiest way is:
select to_char(coalesce(avg(f1), 0), 'FM999999.99')
from test
where f2 > 7
You can also do:
select to_char(case when avg(f1) is not null then avg(f1)
else 0
end, 'FM999999.99')
from test
where f2 > 7
coalesce returns the first argument to it that is not null, or null if
they all are. It's actually implemented using the case one, which is
more verbose but more flexible.
Scott