summing values that can be a null or float value

I have a query where I sum a value sum(U_CPA_NUMER), this value can be a NULL or Float values.  If a null is one of the values my result is null. I have tried using a case statement,

sum(case when u_cpa_numer is null then 0 else u_cpa_numer end) but this yields a 0 value only.

I tried excluding null values for this field in my where clause but this wacks out the whole query.  Please help.
wilconAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
joshbulaConnect With a Mentor Commented:
SUM(ISNULL(U_CPA_NUMBER, 0)) AS U_CPA_NUMBER
0
 
Atdhe NuhiuConnect With a Mentor Commented:
try

sum(isnull(U_CPA_NUMER,0))
0
 
wilconAuthor Commented:
tried that as well still comes back with 0
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Atdhe NuhiuCommented:
Interesting

try this

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test]') AND type in (N'U'))
DROP TABLE [dbo].[test]
go
CREATE TABLE [dbo].[test](
      [id] [smallint] IDENTITY(1,1) NOT NULL,
      [val] [float] NULL
) ON [PRIMARY]


INSERT INTO [test]
           ([val])
     VALUES
           (2)
INSERT INTO [test]
           ([val])
     VALUES
           (3)
INSERT INTO [test]
           ([val])
     VALUES
           (Null)
INSERT INTO [test]
           ([val])
     VALUES
           (4)
INSERT INTO [test]
           ([val])
     VALUES
           (Null)

SELECT * from [test]
SELECT
sum([val]) as s1,
sum(isnull([val],0)) as s2
  FROM [test]
0
 
Atdhe NuhiuCommented:
for me both sums return 9
0
 
wilconAuthor Commented:
is it possible it's because my values are of float datatype
0
 
Atdhe NuhiuCommented:
The example above is a float datatype.

When you run the above SQL do you get 9 for both sums?

I presume the correct sum for query is definitely not 0
0
 
wilconAuthor Commented:
Yes this case works.
0
 
Atdhe NuhiuCommented:
0
 
Atdhe NuhiuCommented:
can you post some example data from your table and the schema?
0
 
Atdhe NuhiuCommented:
If your where query only includes null values however the result will be null and the isnull(U_CPA_NUMER,0) version will be 0

This seems to describe your situation.

Please check the data or try with different criteria
0
 
wilconAuthor Commented:
There is definitely NULL and Float values.
0
 
Atdhe NuhiuCommented:
If you do

select U_CPA_NUMER
from your table
where yourwhereclause

instead of

select sum(U_CPA_NUMER)
from your table
where yourwhereclause

You do get none 0 values as well as nulls?
0
 
wilconAuthor Commented:
found the issue thanks!
0
 
Atdhe NuhiuCommented:
...and it was?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.