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