在Postgres中选择CASE

Question

我正在尝试进行以下咨询：

        Managers | Clerks | Presidents | Analysts | Salesmans
    -------------+---------+--------------+ -----------+-------------
      3            4              1               2               4

到目前为止，我设法做：

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;

，但我似乎无法运行它，它会出错：

错误：" AS"或接近的语法错误
第1行：...较低（job）='推销员'，然后将（job）计数为" salesm ...

）

如何在选择中使用案例来创建单独的列？

Answer 1

您需要将计数围绕 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;

诸如 count()之类的汇总函数忽略零值。CASE表达式返回一个NULL，以使其值与条件不匹配，因此未计数。

使用filter子句：

更简单

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;