PostgreSQL Aggregate Functions (SUM, AVG, COUNT)

Aggregate functions compute a single result from a set of rows. They are the building blocks of any reporting query and are almost always used with GROUP BY.

The Core Aggregates

SELECT
  COUNT(*)           AS total_orders,
  COUNT(DISTINCT customer_id) AS unique_customers,
  SUM(total)         AS revenue,
  AVG(total)         AS avg_order_value,
  MIN(total)         AS smallest_order,
  MAX(total)         AS largest_order
FROM orders
WHERE created_at >= '2025-01-01';

COUNT(*) counts all rows including NULLs. COUNT(column) only counts non-NULL values. COUNT(DISTINCT column) counts unique non-NULL values.

Aggregates with GROUP BY

-- Revenue per category
SELECT
  p.category,
  COUNT(*) AS items_sold,
  SUM(oi.quantity * oi.unit_price) AS revenue,
  ROUND(AVG(oi.unit_price), 2) AS avg_price
FROM order_items oi
JOIN products p ON oi.product_id = p.id
GROUP BY p.category
ORDER BY revenue DESC;

Conditional Aggregation with FILTER

-- Count orders by status in a single query
SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE status = 'completed') AS completed,
  COUNT(*) FILTER (WHERE status = 'pending')   AS pending,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled,
  SUM(total) FILTER (WHERE status = 'completed') AS completed_revenue
FROM orders;

The FILTER clause is a PostgreSQL extension that applies a condition to individual aggregates. It is cleaner than the traditional CASE WHEN approach and easier to read.

Rounding and Formatting

SELECT
  category,
  ROUND(AVG(price), 2) AS avg_price,
  TO_CHAR(SUM(price), 'FM$999,999.00') AS formatted_total
FROM products
GROUP BY category;

Common Use Cases

• Dashboard KPIs: total revenue, active users, conversion rates
• Summarizing data for charts and graphs
• Building pivot-style reports with conditional FILTER clauses
• Calculating running totals and averages for trend analysis