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?
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
