Unexpected behavior with CASE statement

On 10/3/07, Jimmy Choi <yhjchoi@gmail.com> wrote:

I expect to get the following result set:

metric_type | result
------------+-------
0 | 2
1 | 3

Try:

SELECT metric_type
, SUM(CASE metric_type
WHEN 0
THEN 1 / val
WHEN 1
THEN val
END) AS RESULT
FROM metrics
GROUP BY metric_type
ORDER BY metric_type

This will work for this particular example. But what if my case
statement is more complicated than that? Example:

select
metric_type,
case metric_type
when 0 then
sum (1 / val)
when 1 then
sum (val)
when 2 then
max (val)
when 3 then
min (val)
end as result
from metrics
group by metric_type

Thanks!

Jimmy Choi escribi�:

This will work for this particular example. But what if my case
statement is more complicated than that? Example:

select
metric_type,
case metric_type
when 0 then
sum (1 / val)
when 1 then
sum (val)
when 2 then
max (val)
when 3 then
min (val)
end as result
from metrics
group by metric_type

This doesn't make sense. Use separate output columns for the different
aggregates.

--
Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"Crear es tan dif�cil como ser libre" (Elsa Triolet)

"Jimmy Choi" <yhjchoi@gmail.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