Обсуждение: math problem
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
Вложения
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)
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..."
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
Вложения
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..."