Re: All hail Neo!
From: Frank Hamersley <terabitemightbe_at_bigpond.com>
Date: Fri, 28 Apr 2006 08:19:32 GMT
Message-ID: <oek4g.17997$vy1.11003_at_news-server.bigpond.net.au>
>
> select case when count(*) = count(age)
> then avg(age)
> else null
> end as avgage
> from table
Date: Fri, 28 Apr 2006 08:19:32 GMT
Message-ID: <oek4g.17997$vy1.11003_at_news-server.bigpond.net.au>
Frank Hamersley wrote:
> Bob Badour wrote:
[..]
>> I agree it is no problem to write the query to get the average ages >> where the age is known. Using null, how does one write the query to >> get the correct result, ie. the average is unknown ?
>
> select case when count(*) = count(age)
> then avg(age)
> else null
> end as avgage
> from table
Any of you fifth columnists out there care to rub Bobs nose in this query?
I reckon I have graced his killfile and the query was buried so deep in my response to his rant that he prolly would not have even seen it if he had read the post.
It was his challenge and it seems he doesn't have the gonads to stay in the kitchen long enough to see the outcome...so be warned he is unlikely to respond meekly :-).
Or if you think the query is a dud let me know - it works on ASE 12.x and I presume it is generally viable on other SQL engines.
Cheers, Frank. Received on Fri Apr 28 2006 - 10:19:32 CEST