Grouping shows its real power when combined with aggregate functions. For each group you can compute the sum, average, maximum, minimum and so on, each as a single value. Multiple aggregates are computed together in one grouping pass.

-- each aggregate returns one value per group
SUM(amount)              -- total
AVG(amount)              -- average (NULLs are ignored)
MAX(amount), MIN(amount) -- max / min
ROUND(AVG(amount), 0)   -- formatting decimal places

From the orders table, compute per category: order count, total, average (rounded to an integer), maximum, and minimum. Output columns: category, order_count, total_amount, avg_amount, max_amount, min_amount. Sort by total_amount descending.

Note: the Books average = (1200+800+2000)/3 = 1333.33 → 1333 after ROUND.

Expected output (total_amount descending):

Food has only one order, so total = average = max = min = 500.

SELECT
  category,                             -- the grouping key column
  COUNT(*)             AS order_count,  -- count of all rows in the group
  SUM(amount)          AS total_amount, -- total of amount within the group
  ROUND(AVG(amount), 0) AS avg_amount,  -- average, rounded to 0 decimal places (an integer)
  MAX(amount)          AS max_amount,   -- largest value within the group
  MIN(amount)          AS min_amount    -- smallest value within the group
FROM     orders
GROUP BY category                       -- group per category
ORDER BY total_amount DESC;             -- sort by total, descending

/*
  Execution order (logical evaluation order in SQL):
  1. FROM orders                 → read the rows
  2. GROUP BY category           → group by category
  3. aggregate per group         → COUNT/SUM/AVG/MAX/MIN
  4. ORDER BY total_amount DESC  → sort by total, descending
  */

GROUP BY accepts multiple columns. Groups are then formed from rows whose combination of column values matches. With GROUP BY category, status, (Books, completed) and (Books, cancelled) are separate groups. This is the foundation of cross-tabulation — combining analysis dimensions.

-- group per (category, status) combination
SELECT   category, status, COUNT(*), SUM(amount)
FROM     orders
GROUP BY category, status;

From the orders table, compute the order count and total amount per category × status combination. Output columns: category, status, order_count, total_amount. Sort by category, then status, ascending.

Note: 5 distinct (category, status) combinations exist in the data.

Expected output (category, status ascending):

The 7 rows collapse into 5 combination groups.

SELECT
  category,
  status,
  COUNT(*)    AS order_count,  -- count per combination
  SUM(amount) AS total_amount  -- total per combination
FROM     orders
GROUP BY category, status      -- group per "combination" of category and status
ORDER BY category, status;     -- multi-column sort (category first, then status, both ascending)

/*
  Execution order (logical evaluation order in SQL):
  1. FROM orders                → read the rows
  2. GROUP BY category, status  → group per combination
  3. COUNT(*) / SUM(amount)     → aggregate per combination
  4. ORDER BY category, status  → sort by name
  */

There are two kinds of filters. WHERE narrows the raw rows before grouping; HAVING narrows the groups after grouping and aggregation. When a condition involves an aggregate function, HAVING is the tool. The difference between them is the key to understanding SQL's execution order.

-- evaluation order: FROM → WHERE → GROUP BY → aggregate → HAVING → SELECT → ORDER BY
SELECT   category, SUM(amount)
FROM     orders
WHERE    status = 'completed'   -- ① narrow the rows (before aggregation)
GROUP BY category
HAVING   SUM(amount) >= 2000    -- ② narrow the groups (after aggregation)

From the orders table, considering only completed orders, find the categories whose total amount is at least 2000. Output columns: category, order_count, total_amount. Sort by total_amount descending.

Note: the high-value ids 3 (5000) and 7 (9000) are cancelled, so WHERE removes them and they never reach the totals.

Expected output (total_amount descending):

Food only has 500 in completed orders, misses the HAVING threshold (2000), and is excluded.

SELECT
  category,
  COUNT(*)    AS order_count,
  SUM(amount) AS total_amount
FROM     orders
WHERE    status = 'completed'  -- [pre-aggregation filter] keep only completed orders, BEFORE grouping
GROUP BY category              -- group per category
HAVING   SUM(amount) >= 2000   -- [post-aggregation filter] keep only groups whose total is 2000 or more, AFTER aggregating
ORDER BY total_amount DESC;    -- sort by total, descending

/*
  Execution order (logical evaluation order in SQL):
  1. FROM orders                 → read the rows
  2. WHERE status='completed'    → keep completed rows
  3. GROUP BY category           → group by category
  4. HAVING SUM(amount)          → keep groups above the threshold
  5. ORDER BY total_amount DESC  → sort by total, descending
  */

To round off grouping, we cover COUNT's three faces and conditional aggregation. COUNT(*) counts all rows, COUNT(column) counts non-NULL rows, and COUNT(DISTINCT column) counts unique values. FILTER is syntax that attaches a condition to a single aggregate.

COUNT(*)                              -- all rows
COUNT(DISTINCT customer_id)           -- number of distinct customers
COUNT(*) FILTER (WHERE status = 'completed')  -- matching rows only
... / NULLIF(COUNT(*), 0)             -- avoid division by zero

From the orders table, compute per category: total orders, unique customers, completed orders, and completion rate (%). Output columns: category, total_orders, unique_customers, completed_orders, completion_pct. Sort by total_orders descending, breaking ties by category ascending.

Note: Books has 3 orders but only 2 customers (101, 101, 102). Completion rate = completed orders / total orders.

Expected output (total_orders descending):

Books and Toys tie on total_orders (3), so category ascending puts Books first.

SELECT
  category,
  COUNT(*)                                     AS total_orders,     -- [all rows] count every order in the group
  COUNT(DISTINCT customer_id)                  AS unique_customers, -- [unique count] deduplicate customer ids to count people
  COUNT(*) FILTER (WHERE status = 'completed') AS completed_orders, -- [conditional aggregate] count only rows with completed status
  ROUND(
    COUNT(*) FILTER (WHERE status = 'completed') * 100.0            -- multiply by 100.0 to force decimals; this is the percentage numerator
    / NULLIF(COUNT(*), 0), 1)                  AS completion_pct    -- NULLIF guards against division by zero; round to 1 decimal place
FROM     orders
GROUP BY category                                                   -- group per category
ORDER BY total_orders DESC, category;                               -- most orders first; ties sorted by category ascending

/*
  Execution order (logical evaluation order in SQL):
  1. FROM orders                           → read the rows
  2. GROUP BY category                     → group by category
  3. aggregate per group                   → COUNT / DISTINCT customers / completion rate
  4. ORDER BY total_orders DESC, category  → sort by count, descending
  */