Select in Postgres with case

Question

I'm trying to make the following consult:

        Managers | Clerks | Presidents | Analysts | Salesmans
    -------------+---------+--------------+ -----------+-------------

      3            4              1               2               4

So far I manage to do:

select  CASE WHEN lower(job)='salesman' THEN count(job) as "SALESMAN"
 WHEN lower(job)='clerk' THEN count(job)  as "CLERK"
 WHEN lower(job)='manager' THEN count(job)  as "MANAGER"
 WHEN lower(job)='analyst' THEN count(job)  as "ANALYST"
 WHEN lower(job)='president' THEN count(job)  as "PRESIDENT"
from emp
group by job;

But I can't seem to run it, it makes an error:

ERROR: syntax error at or near "as"
LINE 1: ... CASE WHEN lower(job)='salesman' THEN count(job) as "SALESM...

How do I use case in the select to create separate columns?

You can't. You have to make separate `select case` for each column. Or use crosstab query http://stackoverflow.com/questions/3002499/postgresql-crosstab-query/11751905#11751905 — Lemjur, Dec 19 '16 at 17:31

score 2 · Accepted Answer · 2016-12-19T20:15:53.117

You need to put the count around the case:

select count(CASE WHEN lower(job)='salesman' THEN 1 END) as "SALESMAN"
       count(CASE WHEN lower(job)='clerk' THEN 1 END) as "CLERK"
       count(case WHEN lower(job)='manager' THEN 1 END) as "MANAGER"
       count(case WHEN lower(job)='analyst' THEN 1 END)  as "ANALYST"
       count(case WHEN lower(job)='president' THEN 1 END) as "PRESIDENT"
from emp;

Aggregate functions like count() ignore null values. The CASE expression returns a NULL for values not matching the condition and thus those aren't counted.

Or simpler using the filter clause:

select count(*) filter (where lower(job)='salesman') as "SALESMAN"
       count(*) filter (where lower(job)='clerk') as "CLERK"
       count(*) filter (where lower(job)='manager') as "MANAGER"
       count(*) filter (where lower(job)='analyst')  as "ANALYST"
       count(*) filter (where lower(job)='president') as "PRESIDENT"
from emp;

I need the result to be only one row, with your result i get the 5 rows. — , Dec 19 '16 at 18:35
Thanks! That way it works, but can you explain what are you doing in "THEN 1 END" ? How does it work? — , Dec 19 '16 at 18:40

Select in Postgres with case

1 Answers1