FILTER句でCASE文を使わずに条件集計する方法

集計関数(...) FILTER (WHERE 条件式)

SELECT
  COUNT(CASE WHEN status = '完了' THEN 1 END)       AS 完了件数,
  COUNT(CASE WHEN status = '処理中' THEN 1 END)     AS 処理中件数,
  COUNT(CASE WHEN status = 'キャンセル' THEN 1 END) AS キャンセル件数
FROM orders;

SELECT
  COUNT(*) FILTER (WHERE status = '完了')       AS 完了件数,
  COUNT(*) FILTER (WHERE status = '処理中')     AS 処理中件数,
  COUNT(*) FILTER (WHERE status = 'キャンセル') AS キャンセル件数
FROM orders;

SELECT
  department,
  SUM(amount) AS 売上合計,
  SUM(amount) FILTER (WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE)) AS 当月件数,
  SUM(amount) FILTER (WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month' 
                        AND order_date <  DATE_TRUNC('month', CURRENT_DATE)) AS 先月件数
FROM sales
GROUP BY department;