Handling null values when case statement return null postgresql

Question

I have a table called check_n where I have city & value

I wrote a query which has CASE statements in it. Below is my query

select city,
       CASE when (value/10)::integer = 2 
       then 2 END as a,
       CASE when (value/10)::integer = 5 
       then 5 END as b,
       CASE when (value/10)::integer = 3 
       then 3 END as c
from check_n;

Attaching the output in below image

I want output as below:

city, a, b, c
glb, 2, 5, 3

Any help is appreciated. I'm using Postgresql 9.5

Also How to use the function if I have another table chck_n like :

 city |  fact  | value 
------+--------+-------
glb  | male   | 22000
glb  | female | 23000
glb  | total  | 45000

And I want to use this query:

SELECT city,
      CASE WHEN fact = 'male'
           THEN value
      END as males,
      CASE WHEN fact= 'female'
           THEN value
      END as females,
      CASE WHEN fact ='total'
           THEN value
      END as total
FROM chck_n;

Your expected output makes no sense. How can value simultaneously be equal to 2, 5, and 3? Please show us sample data, along with the output you expect. — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 21, 2017 at 10:18

Laurenz Albe · Accepted Answer · 2017-12-21 10:30:46Z

2

If you want one result per city, you could proceed like this:

SELECT city,
       CASE WHEN 2 =ANY (array_agg((value/10)::integer))
            THEN 2
       END as a,
       CASE WHEN 5 =ANY (array_agg((value/10)::integer))
            THEN 5
       END as b,
       CASE WHEN 3 =ANY (array_agg((value/10)::integer))
            THEN 3
       END as c
FROM check_n
GROUP BY city;

 city | a | b | c 
------+---+---+---
 glb  | 2 | 5 | 3
(1 row)

answered Dec 21, 2017 at 10:30

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Venkatesh_CTA Over a year ago

Laurenz Albe Over a year ago

If it is a similar problem, do it similarly. If it is a different case, write a new question.

Vao Tsun · Accepted Answer · 2017-12-21 10:31:13Z

2

I assume with your data set:

with ds as (
select city,
       CASE when (value/10)::integer = 2 
       then 2 END as a,
       CASE when (value/10)::integer = 5 
       then 5 END as b,
       CASE when (value/10)::integer = 3 
       then 3 END as c
from check_n)
select distinct city, max(a)a, max(b) b, max(c) c
from ds
group by city

answered Dec 21, 2017 at 10:31

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Collectives™ on Stack Overflow

Handling null values when case statement return null postgresql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related