September 16, 2011 at 1:55 pm
hi
i have used sum(isnull(amt,0)) from viewname
but it still returns null ,i dotn understand why?
it shoudl return sum,any idea?
September 16, 2011 at 11:32 pm
Hi,
Please post your T-SQL here.
It will better to understand.
Thanks
Shatrughna
Shatrughna
September 17, 2011 at 3:51 am
This is indeed the correct behavior if no rows are matching:
select SUM(ISNULL(object_id, 0))
from tempdb.sys.objects
where type = 'X'
What you may want to do is to swap isnull and sum:
select ISNULL(SUM(object_id), 0)
from tempdb.sys.objects
where type = 'X'
September 17, 2011 at 5:19 am
okbangas (9/17/2011)
This is indeed the correct behavior if no rows are matching:
select SUM(ISNULL(object_id, 0))from tempdb.sys.objects
where type = 'X'
<rant>Correct only in that the mathematically illiterate cretin who determined this part of the SQL specification decided to specify that SQL must do the wrong thing when no rows are involved.</rant>
What you may want to do is to swap isnull and sum:
select ISNULL(SUM(object_id), 0)from tempdb.sys.objects
where type = 'X'
That is indeed the way to fix SQL's "correct" treatment of the empty set to that it does the right thing instead of the "correct" thing.
Tom
September 17, 2011 at 6:11 am
Well, we may have different opinions here. The sum of a column in an empty record set does not make any sense to me. The only aggreate function which makes sense to me is count, which indeed does return 0.
September 17, 2011 at 9:54 am
okbangas (9/17/2011)
Well, we may have different opinions here. The sum of a column in an empty record set does not make any sense to me. The only aggreate function which makes sense to me is count, which indeed does return 0.
Well, in mathematics we want (for what seem to me to be pretty obvious reasons) to preserve the truth of the proposition
SUM(A union B) + sum(A intersection B) = SUM(A)+SUM(B),
whenever A and B are finite sets of numbers.
We also want the sum of a set containing one or more distinct numbers to be the sum of those numbers, so we have
SUM({1}) = 1
SUM({2}) = 2
SUM({1,2}) = SUM({1} union {2}) = SUM({1})+SUM({2})-SUM({1} intersection {2})
3 = 1+2 - SUM(emptyset)
SUM(emptyset) = 0
So it's not really something you can disagree on, unless you want to rebuild the whole of mathematics from the ground up and throw away all the maths that exists already. A century years ago Brouwer had good reason for proposing that we do something almost as radical (completely discarding two valued logic as a means of reasoning in mathematics), but that keeps most of mathematics unchanged (including certainly all of finite set theory, which includes the proposition that the sume of the empty set of numbers is zero) so today mathematicians work with both kinds of logic and keep track of where they lead to differences. I somehow doubt that you have as good a reason for your far more radical suggestion as Brouwer had for his.
Of course you might want the sum of the set {1} to be 7, SUM({2}) to be 8, and SUM({1,2}) to be 10; that's what you would get if you made the sum of the empty set 7 instead of 0. Thatt's logically consistent; the only trouble with it is that it certainly is neither useful nor what we normally mean by the sum of a set of numbers.
Aggregate functions AVG, STDEV, MAX and so on also make sense, but mathematically when applied to the empty set they deliver an indeterminate result (or, deliver no result if you prefer that description) so it's sensible for SQL to deliver NULL for the average of an empty set; but it most certainly is not sensible for SQL to deliver NULL for the sum of an empty set.
Incidentally, aggregate products make sense too, and the product of the empty set is 1.
Tom
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply