Re: Problem With Case Statement and Aggregate Functions

On Fri, 11 May 2007 14:47:04 +1000, Andrew Shea <andrew@octahedron.com.au> wrote:
> The following works as expected:
>
> select (SELECT CASE WHEN (1=2) THEN 0 ELSE sum(count) END) from (
>  select 1 as count union select 2 union select 3
> ) as "temp";
>
> The result is "6".
>
> The following also works as expected:
>
> select count(*) from (
>  select 1 as count union select 2 union select 3
> ) as "temp";
>
> The results is "3".
>
>
> However the following code doesn't work even though it is very similar
> to the first query (that is, and aggregate function within a case
> statement):
>
> select (SELECT CASE WHEN (1=2) THEN 0 ELSE COUNT(*) END) from (
>  select 1 as count union select 2 union select 3
> ) as "temp";
>
> The result is three rows of "1".
>
> So why does the "count" aggregate function within a case statement
> execute on a per row basis whereas the "sum" aggregate within a case
> statement will first group the rows?

The * from count(*) binds to the inner most select where it can draw
data.

Think of it like

select
   (select count('1') from bar)
>from foo

foo and bar have nothing to do with each other so it turns into for each
row in foo count the number of records in bar.

klint.

+---------------------------------------+-----------------+
: Klint Gore                            : "Non rhyming    :
: EMail   : kg@kgb.une.edu.au           :  slang - the    :
: Snail   : A.B.R.I.                    :  possibilities  :
: Mail      University of New England   :  are useless"   :
:           Armidale NSW 2351 Australia :     L.J.J.      :
: Fax     : +61 2 6772 5376             :                 :
+---------------------------------------+-----------------+