Hi All,

Pretty simple question.

I have 3 fields in a record I want to SUM in a query, these 3 fields are
 BCD 2 data type.

Now when I run a query to SUM them for example:

SELECT
transtype,
SUM(comm+comm_management+comm_userdefined) AS commission
FROM trans
GROUP BY transtype

I will get a NULL in the commission field if any one of the 3 fields did
not contain an amount. Now I have tried casting all 3 separately and
tried casting them all together as FLOAT but I still get NULL if any one
of the 3 fields I'm adding is NULL.

So question is how do I fix it?

Thanks guys,

Phil

Phil Read wrote:

> Hi All,
>
> Pretty simple question.
>
> I have 3 fields in a record I want to SUM in a query, these 3 fields
> are   BCD 2 data type.
>
> Now when I run a query to SUM them for example:
>
> SELECT
> transtype,
> SUM(comm+comm_management+comm_userdefined) AS commission
> FROM trans
> GROUP BY transtype
>
> I will get a NULL in the commission field if any one of the 3 fields
> did not contain an amount. Now I have tried casting all 3 separately
> and tried casting them all together as FLOAT but I still get NULL if
> any one of the 3 fields I'm adding is NULL.
>
> So question is how do I fix it?
>
> Thanks guys,
>
> Phil

Kia Orana Phil Read

COALESCE

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz
+
Joan and Jeff Cook
The Cooks Oasis
www.cookislandsoasis.com

"Phil Read" <phil@vizualweb.com> wrote in message
news:188637A5-2496-4A1D-9F4F-395AE689C8D3@news.elevatesoft.com...
>
> I will get a NULL in the commission field if any one of the 3 fields did
> not contain an amount. Now I have tried casting all 3 separately and tried
> casting them all together as FLOAT but I still get NULL if any one of the
> 3 fields I'm adding is NULL.
>

SUM(if(comm = null then 0.00 else comm)) + same for the others

Robert