Skip site navigation (1) Skip section navigation (2)

Re: Problem With Case Statement and Aggregate Functions

From: Klint Gore <kg(at)kgb(dot)une(dot)edu(dot)au>
To: Andrew Shea <andrew(at)octahedron(dot)com(dot)au>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Problem With Case Statement and Aggregate Functions
Date: 2007-05-11 08:23:54
Message-ID: 4644281A106.CC69KG@129.180.47.120 (view raw or flat)
Thread:
Lists: pgsql-bugs
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             :                 :
+---------------------------------------+-----------------+

In response to

pgsql-bugs by date

Next:From: Heikki LinnakangasDate: 2007-05-11 08:26:20
Subject: Re: BUG #3267: Relfilenode
Previous:From: Magnus HaganderDate: 2007-05-11 08:19:22
Subject: Re: Is PostgresSQL supported in Windows Vista?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group