null value in queries to default in zero

Поиск
Список
Период
Сортировка
Искать

null value in queries to default in zero

От:
"Dorward Villaruz" <dorwardv@ntsp.nec.co.jp>
Дата:
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?

Re: null value in queries to default in zero

От:
Scott Lamb <slamb@slamb.org>
Дата:
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
FAQ