Thread: CASE
Hi everyone, I'm trying to figure out how to get CASE working in a sum aggregate function. Pretty simple query: select sum(numbers) from lists where uid=1; It's possible that there may be 0 rows where uid == 1. Instead of returning 1 blank row, I want it to return '0'. So, I tried this: select case(sum(numbers)) when null then 0 else sum(numbers) end from list_results; That... Doesn't work. Any suggestions on how to do this and where I can find some decent information on use of CASE?
On Sun, 18 May 2003, James Taylor wrote: > Hi everyone, I'm trying to figure out how to get CASE working in a sum > aggregate function. Pretty simple query: > > select sum(numbers) from lists where uid=1; > > It's possible that there may be 0 rows where uid == 1. Instead of > returning 1 blank row, I want it to return '0'. So, I tried this: > > select case(sum(numbers)) when null then 0 else sum(numbers) end from > list_results; I believe the case would be: case when sum(numbers) is null then 0 else sum(numbers) end It's more like the ternary operator than a switch statement.
On Mon, 2003-05-19 at 02:59, James Taylor wrote: > Hi everyone, I'm trying to figure out how to get CASE working in a sum > aggregate function. Pretty simple query: > > select sum(numbers) from lists where uid=1; > > It's possible that there may be 0 rows where uid == 1. Instead of > returning 1 blank row, I want it to return '0'. So, I tried this: > > select case(sum(numbers)) when null then 0 else sum(numbers) end from > list_results; Use Coalesce: SELECT coalesce(sum(numbers), 0) FROM lists WHERE uid = 1; To use case, efficiently, if you really want: SELECT CASE WHEN sum_numbers IS NULL THEN 0 ELSE sum_numbers END AS sum_numbers FROM (SELECT sum(numbers)AS sum_numbers FROM lists WHERE uid = 1 ) as tab; -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
On Sun, 18 May 2003 23:59:46 -0700, "James Taylor" <jtx@hatesville.com> wrote: >select case(sum(numbers)) when null then 0 else sum(numbers) end from >list_results; > >That... Doesn't work. SELECT CASE WHEN SUM(numbers) IS NULL THEN 0 ELSE SUM(numbers) END ... or SELECT COALESCE(SUM(numbers), 0) FROM ... ServusManfred
"James Taylor" <jtx@hatesville.com> writes: > select case(sum(numbers)) when null then 0 else sum(numbers) end from > list_results; The reason the above doesn't work is that it expands toCASE WHEN sum(numbers) = null THEN ... which always fails ("= null" does not mean "is null"). Easier would be SELECT COALESCE(sum(numbers), 0) FROM list_results regards, tom lane