will SUM (NULL values) give 'N/A'

Question

Post reply

will SUM (NULL values) give 'N/A'

karthik M

One Orange Chip

Points: 29139
More actions
September 29, 2009 at 10:36 am

#385459

Hi All,
I have one problem.
create table tN
(
id int null
)
insert into tN values (null)
insert into tN values (1)
if i execute the below query, it is showing '1'...but my requirement is if there is any NULL value in the row i want to display 'N/A'.
select sum(id) from tN
i.e i want to display 'N/A' for the above query.
Incase if i have like
insert into tN values (2)
insert into tN values (1)
the above query should display 3.
karthik

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 1

Sorry, wrong post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 2

Try this:

select case when id is null then 'N/A' else convert(varchar, sum(id)) end from tN group by id

The convert function has been used to get the 'N/A' value. You can replace it with 0 (zero) here and the conversion will not be required.

eDIT: Arrgh!! ISNULL is more elegant.

dsdeming Ten Centuries Points: 1236 More actions · Answer 3

Because SUM ignores NULL values, one way to do it is simply to find out whether any row is NULL and if so return N/A. One way to do this is to use a quirk in the COUNT function. COUNT(*) counts all rows, but COUNT(id) counts only rows where id is not null.

create table #N

(

id int null

)

insert into #N values (null)

insert into #N values (1)

select CASE WHEN count(*) <> count(id) THEN 'N/A' ELSE CAST(sum(id) AS varchar(12)) END from #N

truncate table #N

insert into #N values (2)

insert into #N values (1)

select CASE WHEN count(*) <> count(id) THEN 'N/A' ELSE CAST(sum(id) AS varchar(12)) END from #N

drop table #N

arun.sas SSChampion Points: 11831 More actions · Answer 4

create table #N

(

id int null

)

insert into #N values (null)

insert into #N values (1)

insert into #N values (2)

select

sum(isnull(id,0))[total sum],

count(id)[total Valid row],

count(isnull(id,0))[total row]

from #n

select isnull(cast(id as varchar),'N/A')[id] from #n

karthik M One Orange Chip Points: 29139 More actions · Answer 5

I am just wondering becuase select 1+2+3+null gives 'NULL'. So the optimizer has some inteligence here.

But the same optimizer act like a fool when select sum(columnname) executed.

so the problem is with the SUM() function. code which run behind the SUM() function is wrong. so there is a bug in the SUM() function.

As per the DB concept, NULL is unknown, so if we do any operation against NULL, the output of that operation is unknown(i.e NULL).

so SUM() function is not following this rule.

If sql follows SQL-92 standard, then how this one is acceptable? who's mistake is this?

karthik

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

karthikeyan-444867 (9/30/2009)
I am just wondering becuase select 1+2+3+null gives 'NULL'. So the optimizer has some inteligence here.
But the same optimizer act like a fool when select sum(columnname) executed.
so the problem is with the SUM() function. code which run behind the SUM() function is wrong. so there is a bug in the SUM() function.
As per the DB concept, NULL is unknown, so if we do any operation against NULL, the output of that operation is unknown(i.e NULL).
so SUM() function is not following this rule.
If sql follows SQL-92 standard, then how this one is acceptable? who's mistake is this?

You're quoting what happens against ATOMIC operations. Sum is an AGGREGATE function. There was a design question to not include NULL values in the aggregate function, so the NULL's are never part of what is being added up.

It's not a bug at all, it was a concious decision by the design team, and is documented in the BOL articles about aggregation.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

Michael Coles wrote an article about this. Enjoy -

http://www.sqlservercentral.com/articles/Advanced+Querying/gotchasqlaggregatefunctionsandnull/1947/[/url]

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

karthik M One Orange Chip Points: 29139 More actions · Answer 8

Yes...i read the article...

concious decision by the design team

when this decision was taken ? and from which version this decision is being followed?

karthik

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

As far as I know - this has ALWAYS been true. Just doing a simple search in BOL - I can find the reference in 2000, 2005 and 2008 (which represents ALL versions that are published in full in BOL).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?