8. Aggregate Functions · Data Engineering

Aggregate Functions

Aggregate functions summarize many rows into one value. They are the foundation of reporting queries.

Sample table

sql

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  status VARCHAR(20),
  total_amount DECIMAL(10, 2)
);

INSERT INTO orders
VALUES
  (101, 1, "2026-01-05", "paid", 250.00),
  (102, 2, "2026-01-06", "paid", 1200.50),
  (103, 1, "2026-01-10", "cancelled", 80.00),
  (104, 3, "2026-01-11", "paid", 740.25),
  (105, 4, "2026-01-12", "pending", 980.00);

COUNT

COUNT(*) counts rows.

sql

SELECT COUNT(*) AS total_orders
FROM orders;

COUNT(column_name) counts non-null values in that column.

sql

SELECT COUNT(total_amount) AS orders_with_amount
FROM orders;

SUM

SUM adds numeric values.

sql

SELECT SUM(total_amount) AS gross_order_value
FROM orders;

Filter first when the metric should exclude rows.

sql

SELECT SUM(total_amount) AS paid_revenue
FROM orders
WHERE status = "paid";

AVG

AVG calculates the average of non-null values.

sql

SELECT AVG(total_amount) AS average_order_value
FROM orders
WHERE status = "paid";

MIN and MAX

MIN and MAX find the lowest and highest values.

sql

SELECT
  MIN(total_amount) AS smallest_order,
  MAX(total_amount) AS largest_order
FROM orders;

They also work with dates.

sql

SELECT
  MIN(order_date) AS first_order_date,
  MAX(order_date) AS latest_order_date
FROM orders;

Multiple metrics in one query

You can calculate several metrics at once.

sql

SELECT
  COUNT(*) AS total_orders,
  SUM(total_amount) AS total_value,
  AVG(total_amount) AS average_value,
  MIN(total_amount) AS min_value,
  MAX(total_amount) AS max_value
FROM orders;

Aggregates ignore NULL values except COUNT(*), which counts rows regardless of null columns.