Apply a Condition to a Single Aggregate

Count or sum only the rows that match a specific condition without a WHERE clause — using `FILTER (WHERE ...)` in PostgreSQL and SQLite, or `CASE WHEN` inside the aggregate for other engines.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Count total, completed, and cancelled orders in one query

PostgreSQL and SQLite append FILTER (WHERE status = 'complete') directly to COUNT(*) and SUM(amount), restricting each aggregate to matching rows only. MySQL, MariaDB, and SQL Server use CASE WHEN status = 'complete' THEN value END — when the condition is false the expression returns NULL, and COUNT and SUM both ignore NULL values, so only matching rows contribute. All five engines produce the same result: 5 total orders, 3 completed orders, and 550 in completed revenue (100 + 150 + 300).

MySQL MariaDB SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE orders (
  id INT,
  customer VARCHAR(50),
  status VARCHAR(20),
  amount INT
);

INSERT INTO
  orders
VALUES
  (1, 'Alice', 'complete', 100),
  (2, 'Bob', 'pending', 200),
  (3, 'Carol', 'complete', 150),
  (4, 'Dave', 'cancelled', 50),
  (5, 'Eve', 'complete', 300);

Validated query

SQL

SELECT
  COUNT(*) AS total,
  COUNT(
    CASE
      WHEN status = 'complete' THEN 1
    END
  ) AS completed,
  SUM(
    CASE
      WHEN status = 'complete' THEN amount
    END
  ) AS completed_revenue
FROM
  orders;

Expected result

total	completed	completed_revenue
5	3	550

PostgreSQL SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE orders (
  id INT,
  customer VARCHAR(50),
  status VARCHAR(20),
  amount INT
);

INSERT INTO
  orders
VALUES
  (1, 'Alice', 'complete', 100),
  (2, 'Bob', 'pending', 200),
  (3, 'Carol', 'complete', 150),
  (4, 'Dave', 'cancelled', 50),
  (5, 'Eve', 'complete', 300);

Validated query

SQL

SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (
    WHERE
      status = 'complete'
  ) AS completed,
  SUM(amount) FILTER (
    WHERE
      status = 'complete'
  ) AS completed_revenue
FROM
  orders;

Expected result

total	completed	completed_revenue
5	3	550

PostgreSQL and SQLite use `FILTER (WHERE ...)` appended to each aggregate. MySQL, MariaDB, and SQL Server use `CASE WHEN` inside the aggregate.

Useful when

Where this command helps.

counting rows that match two different conditions in a single GROUP BY query
computing a conditional sum alongside an unconditional total in one pass

Explanation

What the command is doing.

SQL's WHERE clause filters rows before grouping and affects every aggregate in the query equally. When you need different conditions on different aggregates in the same GROUP BY — for example counting completed and cancelled orders alongside a total — you must apply the condition inside the aggregate itself. PostgreSQL and SQLite support the SQL-standard FILTER (WHERE condition) clause appended directly to the aggregate call. MySQL, MariaDB, and SQL Server achieve the same result by wrapping the value in a CASE WHEN condition THEN value END expression inside the aggregate. Both techniques produce identical results. The FILTER syntax is more readable when several conditional aggregates appear in one query because it keeps the condition visually separate from the aggregate function.

Apply a Condition to a Single Aggregate

Count total, completed, and cancelled orders in one query

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.