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

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