Aggregation

SELECT COUNT(*) FROM table;
SELECT COUNT(column) FROM table;
SELECT COUNT(DISTINCT column) FROM table;

SELECT
  COUNT(*) AS total_orders,
  COUNT(shipped_date) AS shipped_orders,
  COUNT(DISTINCT user_id) AS unique_customers
FROM orders;

-- total_orders | shipped_orders | unique_customers
-- 1500         | 1230           | 480

Note COUNT(*) counts all rows including NULLs. COUNT(column) skips NULLs. COUNT(DISTINCT column) counts unique non-NULL values. This distinction is a common source of bugs.

SELECT SUM(column), AVG(column) FROM table;

SELECT
  SUM(total_amount) AS revenue,
  AVG(total_amount) AS avg_order_value
FROM orders
WHERE order_date >= '2025-01-01';

-- revenue   | avg_order_value
-- 245890.50 | 163.93

Note Both SUM and AVG ignore NULL values. If all values are NULL, SUM returns NULL (not 0). Wrap with COALESCE(SUM(col), 0) if you need a zero default.

SELECT MIN(column), MAX(column) FROM table;

SELECT
  MIN(price) AS cheapest,
  MAX(price) AS most_expensive,
  MIN(created_at) AS first_product,
  MAX(created_at) AS latest_product
FROM products;

-- cheapest | most_expensive | first_product | latest_product
-- 4.99     | 2499.00        | 2023-03-15    | 2025-11-20

Note MIN and MAX work on numbers, strings, and dates. For strings, they use lexicographic ordering based on the column's collation.

SELECT column, aggregate(col)
FROM table
GROUP BY column;

SELECT
  category_id,
  COUNT(*) AS product_count,
  AVG(price) AS avg_price
FROM products
GROUP BY category_id;

-- category_id | product_count | avg_price
-- 1           | 45            | 29.99
-- 2           | 32            | 89.50

Note Every non-aggregated column in SELECT must appear in GROUP BY (ANSI rule). MySQL historically allowed non-grouped columns with unpredictable results; enable ONLY_FULL_GROUP_BY mode to catch this.

SELECT column, aggregate(col)
FROM table
GROUP BY column
HAVING aggregate_condition;

SELECT
  user_id,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_spent
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 5;

-- Only users with 5 or more orders

Note HAVING filters groups after aggregation. WHERE filters rows before aggregation. A common mistake is using WHERE with aggregate functions - that is a syntax error.

SELECT col1, col2, aggregate(col)
FROM table
GROUP BY col1, col2;

SELECT
  EXTRACT(YEAR FROM order_date) AS order_year,
  status,
  COUNT(*) AS order_count
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date), status
ORDER BY order_year, status;

-- order_year | status    | order_count
-- 2024       | completed | 890
-- 2024       | cancelled | 42
-- 2025       | completed | 1050

Note Grouping by multiple columns creates one group for each unique combination of values. The more columns you group by, the more granular (and more numerous) the groups become.

SELECT col1, col2, aggregate(col)
FROM table
GROUP BY ROLLUP(col1, col2);

SELECT
  category_id,
  brand,
  SUM(price * stock_qty) AS inventory_value
FROM products
GROUP BY ROLLUP(category_id, brand);

-- Includes subtotals per category and a grand total row
-- category_id | brand | inventory_value
-- 1           | Acme  | 5000
-- 1           | NULL  | 12000  (subtotal for category 1)
-- NULL         | NULL  | 45000  (grand total)

Note ROLLUP generates subtotals in a hierarchy. Use GROUPING() function to distinguish real NULLs from subtotal NULLs. MySQL supports WITH ROLLUP syntax; PostgreSQL uses ROLLUP().

SELECT col1, col2, aggregate(col)
FROM table
GROUP BY GROUPING SETS ((col1), (col2), ());

SELECT
  category_id,
  region,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY GROUPING SETS (
  (category_id, region),
  (category_id),
  (region),
  ()
);

-- Returns aggregations at every specified level
-- including per-category, per-region, and grand total

Note GROUPING SETS let you define exactly which grouping combinations you want, unlike ROLLUP which follows a strict hierarchy. Supported in PostgreSQL and SQL Server; MySQL uses ROLLUP only.

aggregate_function(col) FILTER (WHERE condition)

SELECT
  COUNT(*) AS total_orders,
  COUNT(*) FILTER (WHERE status = 'completed') AS completed,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled,
  SUM(total_amount) FILTER (WHERE status = 'completed') AS completed_revenue
FROM orders;

-- total_orders | completed | cancelled | completed_revenue
-- 1500         | 1200      | 85        | 198450.00

Note FILTER is PostgreSQL-specific (ANSI SQL:2003). In MySQL, use SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as the equivalent pattern.

-- PostgreSQL / ANSI
STRING_AGG(column, delimiter)
-- MySQL
GROUP_CONCAT(column SEPARATOR delimiter)

-- PostgreSQL
SELECT
  order_id,
  STRING_AGG(product_name, ', ' ORDER BY product_name) AS products
FROM order_items oi
JOIN products p ON p.id = oi.product_id
GROUP BY order_id;

-- order_id | products
-- 101      | Keyboard, Monitor, Mouse

Note STRING_AGG is standard SQL. MySQL uses GROUP_CONCAT with a default max length of 1024 characters - increase group_concat_max_len if results get truncated.