Thread: Unexpected behavior with CASE statement
Suppose I have the following table named "metrics": metric_type | val ------------+----- 0 | 1 0 | 1 1 | 0 1 | 3 Now suppose I run the following simple query: 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 I expect to get the following result set: metric_type | result ------------+------- 0 | 2 1 | 3 But in reality I get the following error: ERROR: division by zero SQL state: 22012 So it appears that Postgres executes all cases and select the result in the end. Is this expected behavior? Thanks - Jimmy
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! On 10/3/07, Rodrigo De León <rdeleonp@gmail.com> wrote: > 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 >
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