PostgreSQL, SELECT CASE COALESCE

Question

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.

Solution 1

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

Solution 2

You can have this without subquery. You could repeat the expression in the GROUP BY and ORDER BY clause. But it's much simpler to use the ordinal number of the output column instead:

SELECT CASE mycat
         WHEN '1' THEN 'ONE'
         WHEN '2' THEN 'TWO'
         WHEN '3' THEN 'THREE'
         WHEN '4' THEN 'OTHER'
         ELSE          'ZERO'  -- catches all other values
       END AS my_category
    ,  COALESCE(SUM(col1), 0) AS sum1
    ,  COALESCE(SUM(col2), 0) AS sum2
    ,  COALESCE(SUM(col3), 0) AS sum3
FROM   mytable 
GROUP  BY 1
ORDER  BY 1;

I chose the simplest and fastest code. The ELSE branch catches 0, '' and NULL - or any other value not yet filtered! But you say there are no others.

A couple of rants:

• mycat is 'text' column with possible values '0' to '4'.
  This is wrong in two ways.
  1. You obviously also have empty strings ('') and / or NULL values.
  2. integer, smallint, "char" or enum would be sensible choices for the data type. text is not.

• To find out your actual range of values:

  SELECT mycat, count(*) AS ct FROM mytable GROUP BY 1 ORDER BY 2 DESC;
  

  pgAdmin displays empty strings a '', but not NULL with default settings.
  If you are not sure, test with mycat IS NULL. You need to know and understand the difference in many situations.

• This orders by the resulting text in my_category? ONE, OTHER, THREE, TWO, ZERO? I doubt you want that. To keep it simple you could instead output: 0, 1, 2, 3, OTHER.