Thread: Problem With Case Statement and Aggregate Functions

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?

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             :                 :
+---------------------------------------+-----------------+

Andrew Shea <andrew@octahedron.com.au> writes:
> 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";

Lose the underlined SELECT and it will behave the way you expect.
As-is the COUNT is an aggregate of that sub-select, not of the topmost
select.  To be considered an aggregate of the topmost select it has
to reference a variable of that query level.

            regards, tom lane