Thread: 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