Can my DB?

SELECT
  department,
  COUNT(*) AS total_employees,
  COUNT(*) FILTER (WHERE salary > 100000) AS high_earners,
  SUM(salary) FILTER (WHERE employment_type = 'full-time') AS ft_salary_total,
  AVG(salary) FILTER (WHERE hire_date >= '2020-01-01') AS recent_hire_avg_salary
FROM employees
GROUP BY department;

• Works with every aggregate, including STRING_AGG, ARRAY_AGG, and user-defined aggregates.
• Also composes with window functions: SUM(amount) FILTER (WHERE ...) OVER (PARTITION BY ...).

-- Workaround: SUM with IF
SELECT
  department,
  COUNT(*) AS total_employees,
  SUM(IF(salary > 100000, 1, 0)) AS high_earners,
  SUM(IF(employment_type = 'full-time', salary, 0)) AS ft_salary_total,
  AVG(IF(hire_date >= '2020-01-01', salary, NULL)) AS recent_hire_avg_salary
FROM employees
GROUP BY department;

• No FILTER. Use SUM(IF(cond, value, 0)) or SUM(CASE WHEN cond THEN value END).
• For conditional counts, COUNT(IF(cond, 1, NULL)) is idiomatic (NULLs don't count).

-- Workaround: SUM with IF or CASE
SELECT
  department,
  COUNT(*) AS total_employees,
  SUM(IF(salary > 100000, 1, 0)) AS high_earners,
  SUM(CASE WHEN employment_type = 'full-time' THEN salary END) AS ft_salary_total
FROM employees
GROUP BY department;

• No FILTER. Same IF / CASE workaround as MySQL.

SELECT
  department,
  COUNT(*) AS total_employees,
  COUNT(*) FILTER (WHERE salary > 100000) AS high_earners,
  SUM(salary) FILTER (WHERE employment_type = 'full-time') AS ft_salary_total,
  AVG(salary) FILTER (WHERE hire_date >= DATE '2020-01-01') AS recent_hire_avg_salary
FROM employees
GROUP BY department;

• FILTER also composes before OVER on an aggregate window function.
• KEEP (DENSE_RANK FIRST/LAST ...) is a different, ordered-set feature, not a FILTER equivalent.

-- Workaround: CASE expression inside aggregate
SELECT
  department,
  COUNT(*) AS total_employees,
  SUM(CASE WHEN salary > 100000 THEN 1 ELSE 0 END) AS high_earners,
  SUM(CASE WHEN employment_type = 'full-time' THEN salary ELSE 0 END) AS ft_salary_total,
  -- IIF shorthand
  SUM(IIF(employment_type = 'full-time', salary, 0)) AS ft_salary_total_iif
FROM employees
GROUP BY department;

• No FILTER. Use CASE expressions or the two-branch IIF(cond, true_val, false_val) shorthand inside the aggregate.

SELECT
  department,
  COUNT(*) AS total_employees,
  COUNT(*) FILTER (WHERE salary > 100000) AS high_earners,
  SUM(salary) FILTER (WHERE employment_type = 'full-time') AS ft_salary_total
FROM employees
GROUP BY department;

• Added in 3.30.0 (2019-10-04). Works with built-in aggregates and with window functions.

-- Workaround: CASE expression inside aggregate
SELECT
  department,
  COUNT(*) AS total_employees,
  SUM(CASE WHEN salary > 100000 THEN 1 ELSE 0 END) AS high_earners,
  SUM(CASE WHEN employment_type = 'full-time' THEN salary END) AS ft_salary_total
FROM employees
GROUP BY department;

• No FILTER. Use CASE inside the aggregate.

-- Workaround: CASE expression inside aggregate
SELECT
  department,
  COUNT(*) AS total_employees,
  SUM(CASE WHEN salary > 100000 THEN 1 ELSE 0 END) AS high_earners,
  SUM(CASE WHEN employment_type = 'full-time' THEN salary END) AS ft_salary_total
FROM employees
GROUP BY department;

• No FILTER. Use CASE inside the aggregate.

-- Workaround: IFF or CASE expression inside aggregate
SELECT
  department,
  COUNT(*) AS total_employees,
  SUM(IFF(salary > 100000, 1, 0)) AS high_earners,
  SUM(IFF(employment_type = 'full-time', salary, 0)) AS ft_salary_total,
  COUNTIF(salary > 100000) AS high_earners_countif
FROM employees
GROUP BY department;

• No FILTER. COUNTIF(cond) is the built-in shorthand for COUNT(*) FILTER (WHERE cond); for other aggregates, use IFF(cond, value, 0) or CASE.

-- Workaround: IF expression inside aggregate
SELECT
  department,
  COUNT(*) AS total_employees,
  COUNTIF(salary > 100000) AS high_earners,
  SUM(IF(employment_type = 'full-time', salary, 0)) AS ft_salary_total,
  AVG(IF(hire_date >= '2020-01-01', salary, NULL)) AS recent_hire_avg_salary
FROM employees
GROUP BY department;

• No FILTER. COUNTIF(cond) is the built-in shorthand for COUNT(*) FILTER (WHERE cond). No SUMIF / AVGIF; use SUM(IF(...)) and AVG(IF(...)).