Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

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.
0
wilcon
Asked:
wilcon
  • 9
  • 5
2 Solutions
 
joshbulaCommented:
SUM(ISNULL(U_CPA_NUMBER, 0)) AS U_CPA_NUMBER
0
 
Philip PinnellCommented:
try

sum(isnull(U_CPA_NUMER,0))
0
 
wilconAuthor Commented:
tried that as well still comes back with 0
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Philip PinnellCommented:
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
 
Philip PinnellCommented:
for me both sums return 9
0
 
wilconAuthor Commented:
is it possible it's because my values are of float datatype
0
 
Philip PinnellCommented:
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
 
Philip PinnellCommented:
0
 
Philip PinnellCommented:
can you post some example data from your table and the schema?
0
 
Philip PinnellCommented:
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
 
Philip PinnellCommented:
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
 
Philip PinnellCommented:
...and it was?
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now