Re: math problem

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

Re: math problem

От:
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Дата:

On Thu, 25 Jul 2002, Vincent Stoessel wrote:

> cola colb total
>
>       3
> 1
> 4
>
> etc.
>
>
>
> Any work around for this, do I have to use a
> Pg language? I guess I could write a script in perl
> to take of this for me but I would like to tap
> the brains here to see it can al be done in the
> SQL.

If you're trying to get 3, 1, and 4 respectively,
you might want:
coalesce(cola,0)+coalesce(colb,0)

math problem

От:
Vincent Stoessel <vincent@xaymaca.com>
Дата:
Hello All,


I need to find a workaround for my addition problem.

I have a list of values in 3 columns derived from a  left join
where the last column is the sum of the 2 on the left.

i.e.

select cola, colb, sum(cola + colb) from ....

cola  colb total
2     3    5
2     2    4





now my problem comes into play when one of the
columns is empty. I always get a NULL or empty total.
this makes sence since NULL + 5 does not produce anything.


cola colb total

      3
1
4

etc.



Any work around for this, do I have to use a
Pg language? I guess I could write a script in perl
to take of this for me but I would like to tap
the brains here to see it can al be done in the
SQL.
Thanks








-- 
Vincent Stoessel
Linux Systems Developer
vincent xaymaca.com

Re: math problem

От:
Vincent Stoessel <vincent@xaymaca.com>
Дата:
Thank You all for the answer.
I appreciate it a lot. For the record the function is spelled
coalesce.
Later


Roderick A. Anderson wrote:
> On Thu, 25 Jul 2002, Vincent Stoessel wrote:
> 
> 
>>now my problem comes into play when one of the
>>columns is empty. I always get a NULL or empty total.
>>this makes sence since NULL + 5 does not produce anything.
> 
> 
> I think colesce is your friend
> 
> select cola, colb, sum(colesce(cola,0) + colesce(colb,0)) from ....
> 
> I've never had to use it put I've seen this situation addressed here 
> before.   Table 9.3, pg 113 the Elephant book.
> 
> 
> Rod


-- 
Vincent Stoessel
Linux Systems Developer
vincent xaymaca.com

Re: math problem

От:
"Roderick A. Anderson" <raanders@acm.org>
Дата:
On Thu, 25 Jul 2002, Vincent Stoessel wrote:

> now my problem comes into play when one of the
> columns is empty. I always get a NULL or empty total.
> this makes sence since NULL + 5 does not produce anything.

I think colesce is your friend

select cola, colb, sum(colesce(cola,0) + colesce(colb,0)) from ....

I've never had to use it put I've seen this situation addressed here 
before.   Table 9.3, pg 113 the Elephant book.


Rod
-- 
  "Open Source Software - Sometimes you get more than you paid for..."

Re: math problem

От:
"Roderick A. Anderson" <raanders@acm.org>
Дата:
On Fri, 26 Jul 2002, Vincent Stoessel wrote:

> Thank You all for the answer.
> I appreciate it a lot. For the record the function is spelled
> coalesce.

Now how did I do that?  Ah, old eyes, and cut and paste.  Type once, copy 
wrong spelling many times.


Rod
-- 
  "Open Source Software - Sometimes you get more than you paid for..."
FAQ