Postgres case Query issue

Question

I am trying to write a postgres query, which is with CASE statement. The query is as follows:

SELECT "State" AS x, count("Age Group") AS y from test.smp_state_survey where "State" IN 
(CASE 
        WHEN 'State' = 'State' THEN 'State 1','State 2','State 3'
        WHEN 'State' = 'District' THEN 'State 1'
    END) 
  group by x ORDER BY x,y

The above query is showing syntax error at 'State' = 'State'

Whereas, when I executed the below query, i get the appropriate results:

SELECT "State" AS x, count("Age Group") AS y from test.smp_state_survey where "State" IN 
(CASE 
        WHEN 'State' = 'State' THEN 'State 1'
        WHEN 'State' = 'District' THEN 'State 1'
    END) 
  group by x ORDER BY x,y

Please let me know what am I doing wrong.

Edit:

The value after THEN clause is dynamic and it may contain 1 or more values (From a multi select box). I want the query to be executed as

SELECT "State" AS x, count("Age Group") AS y from test.smp_state_survey where "State" IN 
('State 1','State 2','State 3') 
  group by x ORDER BY x,y

Which runs exactly fine, but the problem is that CASE is not returning me the required string.

Prashant Shah · Accepted Answer · 2012-11-09 06:55:59Z

5

Your query should be like this, i hope this will work for you

SELECT  "State" AS x, count("Age Group") AS y from test.smp_state_survey where  
CASE  
        WHEN 'State' = 'State'  THEN  "State" IN  ('State 1' ,'State 3')
        WHEN 'State' = 'District' THEN "State" IN  ('District 1')
    END
  group by x ORDER BY x,y

answered Nov 9, 2012 at 6:55

Prashant Shah

1081 silver badge7 bronze badges

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

Comments

gabrimonfa · Accepted Answer · 2013-11-05 11:03:34Z

2

'State' = 'State' is a tautology. Do you mean "State" = 'State', i.e. the value of the column "State" equal to the string 'State'?

answered Nov 5, 2013 at 11:03

gabrimonfa

212 bronze badges

Comments

Craig Ringer · Accepted Answer · 2012-11-09 06:56:44Z

1

Since you're trying to use IN, you can make use of the fact that IN accepts either a set or an array:

SELECT "State" AS x, count("Age Group") AS y
FROM test.smp_state_survey
WHERE "State" IN (CASE
  WHEN 'State' = 'State' THEN ARRAY['State 1','State 2','State 3']
  WHEN 'State' = 'District' THEN ARRAY['State 1']
END) 
GROUP BY x
ORDER BY x,y

or use @user1327246's rephrasing where you push the IN test into the CASE statement.

answered Nov 9, 2012 at 6:56

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Comments

Shamis Shukoor · Accepted Answer · 2012-11-09 06:32:33Z

0

You cant have multiple values for a single case.

Thats the issue

WHEN 'State' = 'State' THEN 'State 1','State 2','State 3'

answered Nov 9, 2012 at 6:32

Shamis Shukoor

2,5075 gold badges29 silver badges34 bronze badges

4 Comments

Shamis Shukoor Over a year ago

What is exactly your objective when state=state what should the value be ?

aditya Over a year ago

I am not OP. Ankit is an OP. But looking at his condition, he wants to return all three values (state1,state2,state3) when 'State' = 'State'

Shamis Shukoor Over a year ago

then that should be a single string like 'State 1 State 2 State 3'

Ankit Over a year ago

@shamis: I want the output of CASE to be as input of IN so that the query is like -- where "State" IN ('State 1','State 2','State 3')

Collectives™ on Stack Overflow

Postgres case Query issue

4 Answers 4

Comments

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related