SQL (PostgreSQL, MySQL, SQLite) Module 4 – Aggregates and GROUP BY

Conditional aggregation with CASE and FILTER

Count and sum several subsets of rows in one query with CASE inside aggregates or the FILTER clause, and compute percentages over the right denominator.

Intermediate
25 minutes
Examples run with sql.js 1.14.2
By MySmartCoPilot

What you will learn

Count and sum subsets of rows in one query with CASE inside aggregates
Use the FILTER clause where the engine supports it
Compute percentages and ratios over the right denominator

Before you start

On this page

A monthly report rarely asks for one number. It asks for several side by side: how many orders, how many were paid, how many cancelled, how much money came in. Each of those counts a different subset of the same rows. You could run one query per number, but conditional aggregation gets them all from a single query, as the columns of one result.

Ten orders over two months are enough to show every pattern. The scripts run in SQLite 3.49.1 (from sql.js 1.14.2), and the lesson ends with what changes in PostgreSQL and MySQL.

Rows or columns

The query you already know groups by status and returns one row per status:

One row per status SQL · status_rows.sql

-- Ten orders over two months. status is 'paid', 'cancelled' or 'refunded';
-- method is how the customer paid (upi, card or cash); amounts are in paise.
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  city         TEXT    NOT NULL,
  placed_on    TEXT    NOT NULL,
  status       TEXT    NOT NULL,
  method       TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1,  'Pune',  '2026-01-05', 'paid',      'upi',  45000),
  (2,  'Kochi', '2026-01-09', 'cancelled', 'card', 12000),
  (3,  'Pune',  '2026-01-14', 'paid',      'cash', 30000),
  (4,  'Pune',  '2026-01-22', 'paid',      'upi',   8000),
  (5,  'Kochi', '2026-01-28', 'refunded',  'upi',  99000),
  (6,  'Kochi', '2026-02-03', 'paid',      'upi',  15000),
  (7,  'Pune',  '2026-02-11', 'cancelled', 'upi',  52000),
  (8,  'Kochi', '2026-02-17', 'paid',      'card',  7000),
  (9,  'Pune',  '2026-02-20', 'paid',      'upi',  26000),
  (10, 'Pune',  '2026-02-26', 'paid',      'card', 18000);

-- GROUP BY gives one row per status: correct, but not the shape of a report.
SELECT status, count(*) AS n_orders
FROM orders
GROUP BY status
ORDER BY status;

Output

┌───────────┬──────────┐
│  status   │ n_orders │
├───────────┼──────────┤
│ cancelled │ 2        │
│ paid      │ 7        │
│ refunded  │ 1        │
└───────────┴──────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < status_rows.sql

The numbers are right, but a report wants them as columns of one row, next to the total, and with a money column too. Grouping by status cannot give that shape.

CASE inside the aggregate

The trick is to put a CASE expression inside each aggregate. For every row, the CASE decides what that row contributes: 1 if it belongs to the subset and 0 if it does not (or, for a money column, its amount or 0). The aggregate then adds up only what belongs:

Several counts and a sum in one query SQL · case_in_sum.sql

-- Ten orders over two months. status is 'paid', 'cancelled' or 'refunded';
-- method is how the customer paid (upi, card or cash); amounts are in paise.
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  city         TEXT    NOT NULL,
  placed_on    TEXT    NOT NULL,
  status       TEXT    NOT NULL,
  method       TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1,  'Pune',  '2026-01-05', 'paid',      'upi',  45000),
  (2,  'Kochi', '2026-01-09', 'cancelled', 'card', 12000),
  (3,  'Pune',  '2026-01-14', 'paid',      'cash', 30000),
  (4,  'Pune',  '2026-01-22', 'paid',      'upi',   8000),
  (5,  'Kochi', '2026-01-28', 'refunded',  'upi',  99000),
  (6,  'Kochi', '2026-02-03', 'paid',      'upi',  15000),
  (7,  'Pune',  '2026-02-11', 'cancelled', 'upi',  52000),
  (8,  'Kochi', '2026-02-17', 'paid',      'card',  7000),
  (9,  'Pune',  '2026-02-20', 'paid',      'upi',  26000),
  (10, 'Pune',  '2026-02-26', 'paid',      'card', 18000);

-- One pass over the table: each CASE turns a row into 1 or 0 (or into its amount).
SELECT count(*)                                                AS n_orders,
       sum(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)        AS paid,
       sum(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END)   AS cancelled,
       sum(CASE WHEN status = 'paid' THEN amount_paise ELSE 0 END) / 100.0
                                                               AS paid_rupees
FROM orders;

Output

┌──────────┬──────┬───────────┬─────────────┐
│ n_orders │ paid │ cancelled │ paid_rupees │
├──────────┼──────┼───────────┼─────────────┤
│ 10       │ 7    │ 2         │ 1490.0      │
└──────────┴──────┴───────────┴─────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < case_in_sum.sql

Counts as rows, or as columns of one row

Text description of the diagram

The diagram starts from the lesson's ten orders: 7 paid, 2 cancelled and 1 refunded. Two arrows lead to two results.

GROUP BY status gives one row per status: paid 7, cancelled 2 and refunded 1.
A CASE expression inside each aggregate gives a single row with one column per subset: n_orders 10, paid 7 and cancelled 2, side by side, as a report shows them.

sum(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) counts the paid orders: each paid row adds 1, every other row adds 0. The last column uses the same idea with the amount instead of 1. All four columns come from the same ten rows, in one query.

Add GROUP BY, and you get those columns for every group. The rows come from the grouping, the columns from the CASE expressions, which is how a pivot table is made in SQL:

One row per month, one column per status SQL · per_month.sql

-- Ten orders over two months. status is 'paid', 'cancelled' or 'refunded';
-- method is how the customer paid (upi, card or cash); amounts are in paise.
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  city         TEXT    NOT NULL,
  placed_on    TEXT    NOT NULL,
  status       TEXT    NOT NULL,
  method       TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1,  'Pune',  '2026-01-05', 'paid',      'upi',  45000),
  (2,  'Kochi', '2026-01-09', 'cancelled', 'card', 12000),
  (3,  'Pune',  '2026-01-14', 'paid',      'cash', 30000),
  (4,  'Pune',  '2026-01-22', 'paid',      'upi',   8000),
  (5,  'Kochi', '2026-01-28', 'refunded',  'upi',  99000),
  (6,  'Kochi', '2026-02-03', 'paid',      'upi',  15000),
  (7,  'Pune',  '2026-02-11', 'cancelled', 'upi',  52000),
  (8,  'Kochi', '2026-02-17', 'paid',      'card',  7000),
  (9,  'Pune',  '2026-02-20', 'paid',      'upi',  26000),
  (10, 'Pune',  '2026-02-26', 'paid',      'card', 18000);

-- The same columns for every month: GROUP BY makes the rows, CASE makes the columns.
SELECT substr(placed_on, 1, 7)                                AS month,
       count(*)                                               AS n_orders,
       sum(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)       AS paid,
       sum(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END)  AS cancelled,
       sum(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END)   AS refunded
FROM orders
GROUP BY month
ORDER BY month;

Output

┌─────────┬──────────┬──────┬───────────┬──────────┐
│  month  │ n_orders │ paid │ cancelled │ refunded │
├─────────┼──────────┼──────┼───────────┼──────────┤
│ 2026-01 │ 5        │ 3    │ 1         │ 1        │
│ 2026-02 │ 5        │ 4    │ 1         │ 0        │
└─────────┴──────────┴──────┴───────────┴──────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < per_month.sql

February has no refunded order, and its refunded column shows 0, not NULL. That is the ELSE 0 at work. Without it, CASE returns NULL for the rows that do not match, and sum over nothing but NULLs is NULL.

Four ways to write it

CASE inside sum works in every engine, but there are shorter forms:

Four ways to count a subset SQL · four_ways.sql

-- Ten orders over two months. status is 'paid', 'cancelled' or 'refunded';
-- method is how the customer paid (upi, card or cash); amounts are in paise.
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  city         TEXT    NOT NULL,
  placed_on    TEXT    NOT NULL,
  status       TEXT    NOT NULL,
  method       TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1,  'Pune',  '2026-01-05', 'paid',      'upi',  45000),
  (2,  'Kochi', '2026-01-09', 'cancelled', 'card', 12000),
  (3,  'Pune',  '2026-01-14', 'paid',      'cash', 30000),
  (4,  'Pune',  '2026-01-22', 'paid',      'upi',   8000),
  (5,  'Kochi', '2026-01-28', 'refunded',  'upi',  99000),
  (6,  'Kochi', '2026-02-03', 'paid',      'upi',  15000),
  (7,  'Pune',  '2026-02-11', 'cancelled', 'upi',  52000),
  (8,  'Kochi', '2026-02-17', 'paid',      'card',  7000),
  (9,  'Pune',  '2026-02-20', 'paid',      'upi',  26000),
  (10, 'Pune',  '2026-02-26', 'paid',      'card', 18000);

-- Four ways to count the paid orders and add up their amounts.
SELECT count(*) FILTER (WHERE status = 'paid')            AS with_filter,
       count(CASE WHEN status = 'paid' THEN 1 END)        AS count_case,
       sum(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)   AS sum_case,
       sum(status = 'paid')                               AS sum_condition,
       sum(amount_paise) FILTER (WHERE status = 'paid')   AS paid_paise
FROM orders;

Output

┌─────────────┬────────────┬──────────┬───────────────┬────────────┐
│ with_filter │ count_case │ sum_case │ sum_condition │ paid_paise │
├─────────────┼────────────┼──────────┼───────────────┼────────────┤
│ 7           │ 7          │ 7        │ 7             │ 149000     │
└─────────────┴────────────┴──────────┴───────────────┴────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < four_ways.sql

FILTER: count(*) FILTER (WHERE status = 'paid') hands the aggregate only the rows that pass the condition. It reads the most clearly and works with any aggregate, as paid_paise shows. PostgreSQL has it, and SQLite has had it since version 3.30.0. MySQL 8.4 does not: the same query is a syntax error there.
count(CASE WHEN … THEN 1 END): with no ELSE, the CASE gives NULL for the other rows, and count skips NULLs. It works everywhere.
sum(CASE WHEN … THEN 1 ELSE 0 END): works everywhere.
sum(status = 'paid'): in SQLite and MySQL a comparison is the number 1 or 0, so it can be added up. PostgreSQL has a real boolean type and no sum for it, so there you write count(*) FILTER (…) instead.

Percentages

A share is a count divided by a count, times 100. Here are the paid orders of each month split by payment method:

The payment-method mix of each month SQL · method_mix.sql

-- Ten orders over two months. status is 'paid', 'cancelled' or 'refunded';
-- method is how the customer paid (upi, card or cash); amounts are in paise.
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  city         TEXT    NOT NULL,
  placed_on    TEXT    NOT NULL,
  status       TEXT    NOT NULL,
  method       TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1,  'Pune',  '2026-01-05', 'paid',      'upi',  45000),
  (2,  'Kochi', '2026-01-09', 'cancelled', 'card', 12000),
  (3,  'Pune',  '2026-01-14', 'paid',      'cash', 30000),
  (4,  'Pune',  '2026-01-22', 'paid',      'upi',   8000),
  (5,  'Kochi', '2026-01-28', 'refunded',  'upi',  99000),
  (6,  'Kochi', '2026-02-03', 'paid',      'upi',  15000),
  (7,  'Pune',  '2026-02-11', 'cancelled', 'upi',  52000),
  (8,  'Kochi', '2026-02-17', 'paid',      'card',  7000),
  (9,  'Pune',  '2026-02-20', 'paid',      'upi',  26000),
  (10, 'Pune',  '2026-02-26', 'paid',      'card', 18000);

-- The share of each payment method among the paid orders of each month, in per cent.
SELECT substr(placed_on, 1, 7) AS month,
       count(*) AS paid,
       round(100.0 * count(*) FILTER (WHERE method = 'upi')  / count(*), 1) AS upi_pct,
       round(100.0 * count(*) FILTER (WHERE method = 'card') / count(*), 1) AS card_pct,
       round(100.0 * count(*) FILTER (WHERE method = 'cash') / count(*), 1) AS cash_pct
FROM orders
WHERE status = 'paid'
GROUP BY month
ORDER BY month;

Output

┌─────────┬──────┬─────────┬──────────┬──────────┐
│  month  │ paid │ upi_pct │ card_pct │ cash_pct │
├─────────┼──────┼─────────┼──────────┼──────────┤
│ 2026-01 │ 3    │ 66.7    │ 0.0      │ 33.3     │
│ 2026-02 │ 4    │ 50.0    │ 50.0     │ 0.0      │
└─────────┴──────┴─────────┴──────────┴──────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < method_mix.sql

Two details make the numbers right. WHERE status = 'paid' limits every count to paid orders, so the denominator, count(*), is the number of paid orders of the month. And the expression starts with 100.0, a decimal, so the division keeps its fraction. round(…, 1) comes last, once the percentage is computed.

A common mistake: integer percentages

The same percentage, three ways SQL · integer_percentage.sql

-- 2 of 3 orders were paid. Three ways to write the percentage:
SELECT 100 * 2 / 3          AS int_pct,
       2 / 3 * 100          AS int_first,
       round(100.0 * 2 / 3, 1) AS dec_pct;

Output

┌─────────┬───────────┬─────────┐
│ int_pct │ int_first │ dec_pct │
├─────────┼───────────┼─────────┤
│ 66      │ 0         │ 66.7    │
└─────────┴───────────┴─────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < integer_percentage.sql

All three columns try to say “2 of 3 is 66.7 per cent”. 100 * 2 / 3 multiplies first and then divides two integers, so the fraction is cut off: 66. 2 / 3 * 100 is worse: the integer division 2 / 3 gives 0 before anything is multiplied. Only the version that starts from 100.0 keeps the decimals. PostgreSQL divides integers the same way; MySQL’s / returns a decimal, so the same query gives different answers on different engines unless you write 100.0.

Choose the denominator first

The hardest part of a ratio is not the SQL; it is deciding what to divide by. “Cancellation rate per city” can be read in two ways, and they give different numbers:

One numerator, two denominators SQL · denominators.sql

-- Ten orders over two months. status is 'paid', 'cancelled' or 'refunded';
-- method is how the customer paid (upi, card or cash); amounts are in paise.
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  city         TEXT    NOT NULL,
  placed_on    TEXT    NOT NULL,
  status       TEXT    NOT NULL,
  method       TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1,  'Pune',  '2026-01-05', 'paid',      'upi',  45000),
  (2,  'Kochi', '2026-01-09', 'cancelled', 'card', 12000),
  (3,  'Pune',  '2026-01-14', 'paid',      'cash', 30000),
  (4,  'Pune',  '2026-01-22', 'paid',      'upi',   8000),
  (5,  'Kochi', '2026-01-28', 'refunded',  'upi',  99000),
  (6,  'Kochi', '2026-02-03', 'paid',      'upi',  15000),
  (7,  'Pune',  '2026-02-11', 'cancelled', 'upi',  52000),
  (8,  'Kochi', '2026-02-17', 'paid',      'card',  7000),
  (9,  'Pune',  '2026-02-20', 'paid',      'upi',  26000),
  (10, 'Pune',  '2026-02-26', 'paid',      'card', 18000);

-- "Cancellation rate" depends on what you divide by.
SELECT city,
       count(*) AS n_orders,
       sum(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
       round(100.0 * sum(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END)
             / count(*), 1) AS pct_of_all_orders,
       round(100.0 * sum(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END)
             / sum(CASE WHEN status = 'paid' THEN 1 ELSE 0 END), 1) AS per_100_paid
FROM orders
GROUP BY city
ORDER BY city;

Output

┌───────┬──────────┬───────────┬───────────────────┬──────────────┐
│ city  │ n_orders │ cancelled │ pct_of_all_orders │ per_100_paid │
├───────┼──────────┼───────────┼───────────────────┼──────────────┤
│ Kochi │ 4        │ 1         │ 25.0              │ 50.0         │
│ Pune  │ 6        │ 1         │ 16.7              │ 20.0         │
└───────┴──────────┴───────────┴───────────────────┴──────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < denominators.sql

pct_of_all_orders divides by every order the city placed, which answers “what share of orders were cancelled?”. The other column divides by the paid orders only, which answers a different question: “how many cancellations for every 100 successful orders?”. Kochi’s 25 per cent and 50 per 100 describe the same single cancellation. Neither is wrong, but a report must say which one it shows, and the same name must mean the same denominator everywhere.

Two more traps sit in the denominator:

NULLs. If some rows have no status yet, count(*) counts them and count(status) does not. Decide whether they belong to the population before you divide.
Zero. A group can have nothing to divide by. Kolkata below has no paid order yet:

A share whose denominator is 0 SQL · divide_by_zero.sql

-- Kolkata has orders, but none of them was paid yet.
CREATE TABLE orders (id INTEGER PRIMARY KEY, city TEXT, status TEXT, method TEXT);
INSERT INTO orders VALUES
  (1, 'Kolkata', 'cancelled', 'upi'),
  (2, 'Kolkata', 'cancelled', 'card'),
  (3, 'Surat',   'paid',      'upi'),
  (4, 'Surat',   'paid',      'cash');

-- The UPI share of paid orders: Kolkata divides by 0. NULLIF(x, 0) turns a 0 into NULL,
-- so every engine returns NULL instead of an engine-specific answer.
SELECT city,
       sum(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid,
       round(100.0 * sum(CASE WHEN status = 'paid' AND method = 'upi' THEN 1 ELSE 0 END)
             / nullif(sum(CASE WHEN status = 'paid' THEN 1 ELSE 0 END), 0), 1) AS upi_pct_of_paid
FROM orders
GROUP BY city
ORDER BY city;

Output

┌─────────┬──────┬─────────────────┐
│  city   │ paid │ upi_pct_of_paid │
├─────────┼──────┼─────────────────┤
│ Kolkata │ 0    │                 │
│ Surat   │ 2    │ 50.0            │
└─────────┴──────┴─────────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < divide_by_zero.sql

SQLite returns NULL when it divides by zero, and so does MySQL (with a warning); PostgreSQL stops the whole query with a division-by-zero error. nullif(denominator, 0) turns a zero denominator into NULL first, so every engine returns NULL for Kolkata and the query still runs. An empty cell is the honest answer: there is no share of nothing.

How the engines differ

Conditional aggregation in PostgreSQL, MySQL and SQLite
Criterion	PostgreSQL 18	MySQL 8.4	SQLite 3.49
count(*) FILTER (WHERE …)	Yes	No: a syntax error	Yes, from 3.30.0
sum(CASE WHEN … THEN 1 ELSE 0 END)	Yes	Yes	Yes
count(CASE WHEN … THEN 1 END)	Yes	Yes	Yes
sum(status = 'paid')	No: there is no sum of booleans	Yes: a comparison is 1 or 0	Yes: a comparison is 1 or 0
100 * 2 / 3	66	66.6667	66
A division by zero	An error	NULL, with a warning	NULL

SQLite Online (SQL Playground) Build a pivot-style summary of your own CSV file with CASE or FILTER: import it as a table, and SQLite runs in your browser.

Key takeaways

Conditional aggregation computes several subsets in one query: GROUP BY makes the rows, a CASE or FILTER inside each aggregate makes the columns.
sum(CASE WHEN … THEN 1 ELSE 0 END) and count(CASE WHEN … THEN 1 END) work in every engine; keep the ELSE 0 when an empty subset should show 0 rather than NULL.
FILTER (WHERE …) is the clearest form in PostgreSQL and SQLite 3.30+; MySQL lacks it but can add up comparisons, which PostgreSQL cannot.
Start a percentage with 100.0 so the division keeps its fraction, and round last.
Decide the denominator before writing the query, count NULLs on purpose, and guard against zero with nullif(…, 0).

Exercise

Exercise · Medium · SQL

A monthly row of order figures

The table orders has one row per order:

id: the order's number;
placed_on: the day it was placed, as text such as 2026-01-05;
status: paid, cancelled or refunded, or NULL while the order is still being processed;
amount_paise: the amount in paise.

Write a query that returns one row per month, oldest month first, with five columns:

month: the month as text, such as 2026-01;
orders: every order placed in that month, whatever its status;
paid_orders: the orders whose status is paid;
cancelled_orders: the orders whose status is cancelled;
cancelled_pct: cancelled orders as a percentage of all the month's orders, rounded to 1 decimal place.

A month without any cancelled order shows 0 and 0.0, not NULL. Orders without a status count in orders only. The order of the rows is checked.

Starter code · kpis.sql

-- One row per month: all orders, paid, cancelled, and the cancelled share in per cent.
SELECT substr(placed_on, 1, 7) AS month,
       count(*) AS orders,
       0 AS paid_orders,
       0 AS cancelled_orders,
       0 AS cancelled_pct
FROM orders
GROUP BY month;

The sample tests · tests.yaml

# Sample tests: each one builds a small orders table, runs your query on it and compares the rows it returns, in
# order.
tests:
  - name: counts each status per month and the cancelled share
    ordered: true
    setup: |
      CREATE TABLE orders (id INTEGER PRIMARY KEY, placed_on TEXT, status TEXT, amount_paise INTEGER);
      INSERT INTO orders VALUES
        (1, '2026-02-03', 'paid',      15000),
        (2, '2026-01-05', 'paid',      45000),
        (3, '2026-01-09', 'cancelled', 12000),
        (4, '2026-02-11', 'cancelled', 52000),
        (5, '2026-01-14', 'paid',      30000),
        (6, '2026-01-22', 'paid',       8000),
        (7, '2026-01-28', 'refunded',  99000),
        (8, '2026-02-17', 'paid',       7000);
    expected:
      columns: [month, orders, paid_orders, cancelled_orders, cancelled_pct]
      rows:
        - ['2026-01', 5, 3, 1, 20.0]
        - ['2026-02', 3, 2, 1, 33.3]
  - name: shows 0 and 0.0 for a month without cancellations
    ordered: true
    setup: |
      CREATE TABLE orders (id INTEGER PRIMARY KEY, placed_on TEXT, status TEXT, amount_paise INTEGER);
      INSERT INTO orders VALUES
        (1, '2026-03-02', 'paid', 21000),
        (2, '2026-03-09', 'paid', 18000),
        (3, '2026-03-16', 'paid', 26000),
        (4, '2026-03-30', 'paid', 11000);
    expected:
      columns: [month, orders, paid_orders, cancelled_orders, cancelled_pct]
      rows:
        - ['2026-03', 4, 4, 0, 0.0]
  - name: counts orders without a status in the total only
    ordered: true
    setup: |
      CREATE TABLE orders (id INTEGER PRIMARY KEY, placed_on TEXT, status TEXT, amount_paise INTEGER);
      INSERT INTO orders VALUES
        (1, '2026-04-01', 'paid',      30000),
        (2, '2026-04-07', NULL,        14000),
        (3, '2026-04-12', 'cancelled',  9000),
        (4, '2026-04-29', NULL,        22000),
        (5, '2026-05-03', 'cancelled',  6000),
        (6, '2026-05-04', 'refunded',  17000),
        (7, '2026-05-21', 'paid',      40000);
    expected:
      columns: [month, orders, paid_orders, cancelled_orders, cancelled_pct]
      rows:
        - ['2026-04', 4, 1, 1, 25.0]
        - ['2026-05', 3, 1, 1, 33.3]

A hint

Group by the month (substr(placed_on, 1, 7)) and give each figure its own aggregate: count(*) for all orders, and sum(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) for one status. For the percentage, start from 100.0 so that the division keeps its fraction, and round last.

The sample tests run on this device, in your browser (sql.js): nothing is sent to mysmartcopilot.com. The first run downloads SQLite (about 0.7 MB), which is kept for the next runs. A check in your browser is feedback for you, not proof that the code is right for every input.

Check yourself

5 questions about this lesson. Every answer and why it is right is on the page, behind “Show the answer”. Your score stays in this browser.

Question 1 of 5 What does this query return in SQLite?
Read the code, then choose one answer.
```
CREATE TABLE orders (id INTEGER, status TEXT);
INSERT INTO orders VALUES (1, 'paid'), (2, 'cancelled'), (3, 'paid'), (4, NULL);
SELECT count(CASE WHEN status = 'paid' THEN 1 END) AS paid,
       sum(CASE WHEN status = 'refunded' THEN 1 END) AS refunded
FROM orders;
```
paid is 2 and refunded is 0 paid is 4 and refunded is 0 paid is 2 and refunded is 4 paid is 2 and refunded is NULL
Show the answer to question 1

Answer: paid is 2 and refunded is NULL

A CASE without ELSE gives NULL for the rows that do not match. count skips NULLs, so it counts the two paid rows. No row is refunded, so sum sees only NULLs and returns NULL; write ELSE 0 when an empty subset should add up to 0.
Question 2 of 5 2 of 3 orders were paid. What does this query print?
What does this program print? Choose one answer.
```
-- 2 of 3 orders were paid. Three ways to write the percentage:
SELECT 100 * 2 / 3          AS int_pct,
       2 / 3 * 100          AS int_first,
       round(100.0 * 2 / 3, 1) AS dec_pct;
```
┌─────────┬───────────┬─────────┐ │ int_pct │ int_first │ dec_pct │ ├─────────┼───────────┼─────────┤ │ 67 │ 0 │ 66.7 │ └─────────┴───────────┴─────────┘ ┌─────────┬───────────┬─────────┐ │ int_pct │ int_first │ dec_pct │ ├─────────┼───────────┼─────────┤ │ 66 │ 66 │ 66.7 │ └─────────┴───────────┴─────────┘ ┌─────────┬───────────┬─────────┐ │ int_pct │ int_first │ dec_pct │ ├─────────┼───────────┼─────────┤ │ 66 │ 0 │ 66.7 │ └─────────┴───────────┴─────────┘ ┌─────────┬───────────┬─────────┐ │ int_pct │ int_first │ dec_pct │ ├─────────┼───────────┼─────────┤ │ 66.7 │ 66.7 │ 66.7 │ └─────────┴───────────┴─────────┘
Show the answer to question 2
Answer: it prints
```
┌─────────┬───────────┬─────────┐
│ int_pct │ int_first │ dec_pct │
├─────────┼───────────┼─────────┤
│ 66      │ 0         │ 66.7    │
└─────────┴───────────┴─────────┘
```
With two integers SQLite divides as integers and cuts the fraction off. 100 * 2 / 3 is 200 / 3, so 66 (not rounded to 67). 2 / 3 * 100 divides first: 2 / 3 is 0, and 0 times 100 is 0. Starting from 100.0 makes the division decimal, and round(…, 1) gives 66.7.
Question 3 of 5 Which of these count the paid orders in MySQL 8.4?
Choose every answer that is right.

sum(status = 'paid') sum(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) count(CASE WHEN status = 'paid' THEN 1 END) count(*) FILTER (WHERE status = 'paid')
Show the answer to question 3
Answer:
- sum(status = 'paid')
- sum(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)
- count(CASE WHEN status = 'paid' THEN 1 END)
MySQL 8.4 has no FILTER clause, so the FILTER form is a syntax error there. The two CASE forms work in every engine, and sum(status = 'paid') works in MySQL and SQLite, where a comparison is the number 1 or 0. In PostgreSQL, which has a real boolean type, use FILTER or CASE instead.
Question 4 of 5 A city placed 6 orders: 4 paid, 1 cancelled and 1 refunded. The report asks "what share of orders was cancelled?". Which calculation answers it?
Choose one answer.

1 divided by 2, 50 per cent 1 divided by 4, 25 per cent 1 divided by 6, about 16.7 per cent 1 divided by 5, 20 per cent

Show the answer to question 4

Answer: 1 divided by 6, about 16.7 per cent

"Share of orders" means the population is every order the city placed, cancelled ones included: 1 of 6. Dividing by the paid orders (4) or by the orders that were not refunded (5) answers other questions, which a report would have to name differently.
Question 5 of 5 3 of 8 orders were cancelled. What does round(100.0 * 3 / 8, 1) return?
Type a number.
Your answer

Show the answer to question 5

Answer: 37.5

100.0 times 3 is 300.0, and 300.0 / 8 is 37.5; rounding to one decimal place leaves it as 37.5. Had the expression started with the integer 100, SQLite and PostgreSQL would have returned 37.

References

PostgreSQL documentation: Value Expressions (aggregate expressions and FILTER) (The PostgreSQL Global Development Group)
PostgreSQL documentation: Aggregate Functions (The PostgreSQL Global Development Group)
PostgreSQL documentation: Conditional Expressions (CASE, NULLIF) (The PostgreSQL Global Development Group)
PostgreSQL documentation: Mathematical Functions and Operators (The PostgreSQL Global Development Group)
PostgreSQL documentation: Error Codes (division_by_zero) (The PostgreSQL Global Development Group)
SQLite: Built-in Aggregate Functions (SQLite)
SQLite: SQL Language Expressions (SQLite)
SQLite: Datatypes In SQLite (SQLite)
SQLite: Release History (version 3.30.0) (SQLite)
MySQL 8.4 Reference Manual: Aggregate Function Descriptions (Oracle Corporation)
MySQL 8.4 Reference Manual: Comparison Functions and Operators (Oracle Corporation)
MySQL 8.4 Reference Manual: Arithmetic Operators (Oracle Corporation)
MySQL 8.4 Reference Manual: Server SQL Modes (ERROR_FOR_DIVISION_BY_ZERO) (Oracle Corporation)

Report a problem with this lesson

Your country

Conditional aggregation with CASE and FILTER

What you will learn

Before you start

Rows or columns

CASE inside the aggregate

Four ways to write it

Percentages

A common mistake: integer percentages

Choose the denominator first

How the engines differ

Key takeaways

Exercise

A monthly row of order figures

Results of the sample tests

Check yourself

References

Related tools