PostgreSQL, SELECT CASE COALESCE
I need to "name" the categories: mycat
- a column text
with possible values '0'
- '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;
This works fine, but I have some bug in my program that writes very rarely null
(or ''
as I can see in pgAdmin). In such cases, I must assume that is the ''
same as '0'
. But I can't figure it out!
I try like this:
SELECT CASE COALESCE(mycat, '0')
But that doesn't solve it at all.
How do you get what ''
will be summarized and grouped together with the '0'
category?
PostgreSQL 9.3, Windows.
source to share
you need to use COALESCE in group by
and order by
also similar to how you planned to change the expression case
, but postgres gives an error, so another option is to wrap your statement in a subquery and dogroup 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
source to share
You can have it without a subquery. You can repeat the expression in the GROUP BY and ORDER BY clause. But instead, it is much easier to use the ordinal of the output column:
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. Affiliate ELSE catches 0
, ''
and NULL
- or any other value not yet filtered! But you say there are no others.
Several statements:
-
mycat is 'text' column with possible values '0' to '4'.
This is wrong for two reasons.- You also have empty lines (
''
) and / orNULL
. -
integer
,smallint
,"char"
Orenum
will a reasonable choice for the data type.text
not.
- You also have empty lines (
-
To find out your actual range of values:
SELECT mycat, count(*) AS ct FROM mytable GROUP BY 1 ORDER BY 2 DESC;
pgAdmin displays blank lines a '' but not
NULL
with default settings.
If unsure, check withmycat IS NULL
. You need to know and understand the difference in many situations. -
Does this order the resulting text into
my_category
?ONE, OTHER, THREE, TWO, ZERO
? I doubt you want that. To make this easy, you could bring:0, 1, 2, 3, OTHER
.
source to share