More NULLs - need help BAD !
-
Hello...
When running an aggregate function which contains one ore more NULLS in the data, SQL Server simply removes them from the calculation and states at the end of the query:
"Warning, NULL values were removed by an aggregate function".
My question:
Is there a way to tell SQL Server to NOT remove NULL values from aggregations and simply return NULL if any NULLS exist in the data set ?
Any help is appreciated... - B
-
You might try to put the field into a isnull function and force it to return a value that way.
For example:
select field1, isnull(field2, '<None>') from table
group by field1, isnull(field2, '')
-
Thanks but no go.
I do NOT wan a value back - I want NULL as the total result.
I did however try this:
SELECT SUM(ISNULL(VAL, NULL)) FROM
(
SELECT 1 AS Val
union
SELECT 2
union
SELECT 3
union
SELECT NULL
) X
But that behaves the same and returns:
-----------
6
(1 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.
-
Rather
select field1, isnull(field2, '<None>') from table
group by field1, isnull(field2, '<None>')
-
quote:
Thanks but no go.I do NOT wan a value back - I want NULL as the total result.
I did however try this:
SELECT SUM(ISNULL(VAL, NULL)) FROM
(
SELECT 1 AS Val
union
SELECT 2
union
SELECT 3
union
SELECT NULL
) X
But that behaves the same and returns:
-----------
6
(1 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.
Only if all are NULL and there is some other criteria that would force at least one row to output in order to display data for another column.
-
SET ANSI_WARNINGS On
SELECT Case When Sum(Case When Val Is NULL
Then 1
Else 0 End)>0
Then NULL
Else SUM(VAL) End
FROM (
SELECT 1 AS Val UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT NULL UNION
SELECT NULL) X -
OR
If exists( select * from table where val is null)
select 'null'
else
select sum()....
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://qa.sqlservercentral.com/bestof/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Agrees, costs of sum versus check and real sum. Sounded like he wanted his solution.
-
Thanks all...
I was hoping for perhaps a simple "SET Statement" solution, like:
< SET NULL_AGGREGATIONS ON >
or something like that...
FYI,
I have several series of data.
Each series has data every day, but, some series are older or newer than others.
If I run an aggregation on the data from 1/1/2000 until 1/1/2001 and one of the data series began on 6/1/2000, then the whole aggregation is a wash and should return NULL.
I'm just going to check and store each series Begin and End dates in a root table and check values against those dates before the main query. Seems like it will save alot of procesing time.
- B
-
Hi Bill,
quote:
I have several series of data.Each series has data every day, but, some series are older or newer than others.
I'm not sure if I understand why you need to store the same series several times?
If the series of data is of the same day, the data should also the same. No matter at what point you store it afterwards.
For example for a common stock there isn't a closing quote of $10 and some time later one of $12 ceteris paribus.
quote:
If I run an aggregation on the data from 1/1/2000 until 1/1/2001 and one of the data series began on 6/1/2000, then the whole aggregation is a wash and should return NULL.
I use SQL Server heavily for returning statistical data derived from stock quotes series.
If I calculate the SUM(closing Quotes)/Count(Trading days) from the beginning of the year till end or from 1/6 of the year till the end, both results should be pretty much the same. And if you do not need a precise and accurate result, both results are statistically just fine.
What are you doing there?
Frank
Quoting isn't easy
Edited by - Frank Kalis on 09/15/2003 07:43:42 AM
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank-
I've spoken w/ you before in this forum I think...
Yes, I'm running performance numbers on financial returns. We have our own Mutual Funds here and are keeping track of their performance. Some have been around for many years, others only a few months. If we try to run an "Attribution Analysis" over a period of time - I need to make sure that if the users select a period of time outside the range of any Funds Inception or termination, that I return NULL.
Still need to discuss with the powers that be here about how exactly this should be done, but it seems to be the correct way to handle things.
- B
-
quote:
Frank-I've spoken w/ you before in this forum I think...
aargh, my memory, yes I remember
quote:
Yes, I'm running performance numbers on financial returns. We have our own Mutual Funds here and are keeping track of their performance. Some have been around for many years, others only a few months. If we try to run an "Attribution Analysis" over a period of time - I need to make sure that if the users select a period of time outside the range of any Funds Inception or termination, that I return NULL.
what about checking at first the MIN date for any fund and suggesting this as default date at the client?
Or checking this after there funds in question were selected.
However, I think this is more client related and should be processed in your application.
quote:
Still need to discuss with the powers that be here about how exactly this should be done, but it seems to be the correct way to handle things.
if you have all data nicely selected, you should be as precise as possible. (....unless the performance numbers make your company look bad
Frank
Edited by - Frank Kalis on 09/15/2003 07:57:37 AM
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank-
Checking the MIN date is exactly what I'm doing now. The problem is/was, in our "Model Analysis", the Model includes typically 5 Funds - so I have to check the MIN dates for Each Fund. I was trying to find a "tricky" way to handle the checking without a "Brute Force" MIN date check for each fund in the model. I was hoping to have the performance calculation itself handle the MIN checking for NULLS rather than a 2 step process : Check MINS, then run performance. Think it's all under control now - need to test the accuracy of my algorthims, get it working, and then perhaps do some tuning/refactoring.
Thanks again to all for the input.
- B
-
quote:
Frank-Checking the MIN date is exactly what I'm doing now. The problem is/was, in our "Model Analysis", the Model includes typically 5 Funds - so I have to check the MIN dates for Each Fund. I was trying to find a "tricky" way to handle the checking without a "Brute Force" MIN date check for each fund in the model. I was hoping to have the performance calculation itself handle the MIN checking for NULLS rather than a 2 step process : Check MINS, then run performance. Think it's all under control now - need to test the accuracy of my algorthims, get it working, and then perhaps do some tuning/refactoring.
Nice try, even if find your tricky way, next problem would be to make 'automatically' sure that all funds have the same base date for performance tracking. It's not very meaningful to relate the performance of one fund from 1990-1995 to the performance of some other from 1997-2002.
See what I mean?
Both include a five year range, but the result are definitely nonsense!
So I think your approach is really OK to check first and then benchmark.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Not sure if this is helpful, but could you make use of the fact that count(*) includes nulls, whereas count(field1) will exclude them, and then exclude groups where the result is different?
For example;
SELECT sum(Field1), count(*), count(Field1)
FROM Table1
HAVING count(*) = count(Field1)
Just a thought.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply