Re: 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(at)octahedron(dot)com(dot)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(at)kgb(dot)une(dot)edu(dot)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(at)octahedron(dot)com(dot)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