I have a rather annoying little problem thanks to the way SQL treats
nulls in aggregate functions. Normally nulls are ignored in
aggregates, like they are interpretted as meaning "inapplicable".

I have a situation where null definitely means "unknown", so ignoring
the nulls is incorrect and the aggregate (sum() in this case) should
evaluate to null if there is a null input (which is what would happen
in an ordinary SQL expression).

Can anyone think of a nicer way to do it than using a case expression
like this?

select case when count(n) <> case(*) then null
else sum(n) end as total
from foo;

--
Roy

UK Ingres User Association Conference 2011 will be on Tuesday June 7 2011.
Put the date in your diary today.

Does using if instead of case qualify as nicer? (Probably not!...)

select if(count(a)=count(ifnull(a,0)),sum(a)) from foo;


--
geraintjones

Ingres Forums wrote:

Blech!

"Hell no," is my answer. :-)

The unstated background to my question is that my real SELECT statement
is already very gnarly and having to smother it with extra curlicues and
flourishes isn't doing anything for its readability. :-(

I hate SQL....

--
Roy

UK Ingres User Association Conference 2011 will be on Tuesday June 7 2011.
Put the date in your diary today.

Roy Hann wrote:

So far no one has remarked on the stupid type above. That *should*
have been:

select case when count(n) <> count(*) then null
else sum(n) end as total
from foo;

--
Roy

UK Ingres User Association Conference 2011 will be on Tuesday June 7 2011.
Put the date in your diary today.

Roy Hann wrote:

Argh! Type: a meta-typo!!

--
Roy

UK Ingres User Association Conference 2011 will be on Tuesday June 7 2011.
Put the date in your diary today.