PostgreSQL, SELECT CASE COALESCE

I need to ‘name’ categories: mycat is a text column with possible values '0' to '4'.

SELECT CASE mycat                                       
       WHEN '0' THEN 'ZERO'                         
       WHEN '1' THEN 'ONE'                      
       WHEN '2' THEN 'TWO'                      
       WHEN '3' THEN 'THREE'                        
       WHEN '4' THEN 'OTHER'        
   END AS my_category,
   COALESCE(SUM(col1), 0), 
   COALESCE(SUM(col2), 0), 
   COALESCE(SUM(col3), 0) 
   FROM mytable 
GROUP BY mycat 
ORDER BY mycat;

That works OK, but I have some an error in my program which very rarely writes null (or '' as I can see in pgAdmin). In such cases I have to treat that '' the same as '0'. But I can’t get that!

I try like this:

SELECT CASE COALESCE(mycat, '0')

But this doesn’t solve it at all.
How to get that '' will be summed and grouped together with '0' category?

PostgreSQL 9.3, Windows.

Answer

you need to use COALESCE in the group by and order by also similar to how you planned to change the case expression, but postgres is giving error , so another option is to wrap your statement in a subquery and do group by

SELECT my_category, 
       COALESCE(SUM(col1), 0), 
       COALESCE(SUM(col2), 0), 
       COALESCE(SUM(col3), 0) 
FROM
(
SELECT CASE coalesce(mycat ,'0')                                     
       WHEN '0' THEN 'ZERO'                         
       WHEN '1' THEN 'ONE'                      
       WHEN '2' THEN 'TWO'                      
       WHEN '3' THEN 'THREE'                        
       WHEN '4' THEN 'OTHER' 
       WHEN '' THEN 'ZERO'       
   END AS my_category,
   col1,
   col2,
   col3
   FROM mytable 
) T
GROUP BY my_category
ORDER BY my_category