Re: Unexpected behavior with CASE statement

"Jimmy Choi" <yhjchoi(at)gmail(dot)com> writes:
> select
> metric_type,
> case metric_type
> when 0 then
> sum (1 / val)
> when 1 then
> sum (val)
> end as result
> from metrics
> group by metric_type

The reason this does not work is that the aggregate functions are
aggregated without any knowledge of how they might ultimately be used
in the final output row. The fact that the CASE might not actually
demand the value of an aggregate at the end doesn't stop the system from
having to compute it.

You could use a CASE *inside* the SUM() to prevent division by zero
while forming the sum, but on the whole this query seems rather badly
designed. Consider

SELECT 0, sum(1/val) FROM metrics WHERE metric_type = 0
UNION ALL
SELECT 1, sum(val) FROM metrics WHERE metric_type = 1
UNION ALL
...

regards, tom lane