Conditional Aggregation with FILTER / CASE

-- PostgreSQL: FILTER clause
SELECT
  DATE_TRUNC('month', order_date) AS month,
  COUNT(*) AS total_orders,
  COUNT(*) FILTER (WHERE status = 'completed') AS completed,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled,
  SUM(total) FILTER (WHERE status = 'completed') AS completed_revenue
FROM orders
GROUP BY 1
ORDER BY 1;

-- Standard SQL: CASE inside aggregate
SELECT
  department,
  COUNT(CASE WHEN salary > 100000 THEN 1 END) AS high_earners,
  COUNT(CASE WHEN salary <= 100000 THEN 1 END) AS others,
  AVG(CASE WHEN gender = 'F' THEN salary END) AS avg_female_salary,
  AVG(CASE WHEN gender = 'M' THEN salary END) AS avg_male_salary
FROM employees
GROUP BY department;