Re: Problem With Case Statement and Aggregate Functions

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