SQL Aggregate Functions

TLDR: Summarize data

SQL aggregate functions perform calculations across multiple rows to produce a single summary value for each group of data.

Unlike window functions, aggregate functions collapse rows into groups, providing a consolidated view of the data.

They allow you to count, sum, average, and perform other calculations across groups of rows, giving insights into data patterns and totals.

Common aggregate functions include:

SUM(): Adds up values within a group.
AVG(): Calculates the average of values within a group.
COUNT(): Counts the number of rows within each group.
MAX(): Finds the highest value within a group.
MIN(): Finds the lowest value within a group.
GROUP BY: Defines how rows are grouped for aggregation.

Example use cases:

Total Sales by Product

SELECT 
  product_name, 
  SUM(sales) AS total_sales
FROM 
  sales_data
GROUP BY 
  product_name;

Average Order Value by Customer

SELECT 
  customer_id, 
  AVG(order_value) AS average_order_value
FROM 
  orders
GROUP BY 
  customer_id;

Counting Orders Per Month

SELECT 
  MONTH(order_date) AS month, 
  COUNT(*) AS order_count
FROM 
  orders
GROUP BY 
  MONTH(order_date);

Finding Maximum Sale per Region

SELECT 
  region, 
  MAX(sales) AS max_sales
FROM 
  sales_data
GROUP BY 
  region;

Using the OVER() Clause with Aggregate Functions

When used with the OVER() clause, aggregate functions calculate values across the entire dataset or within partitions without grouping the rows into a single result, preserving the row structure.

Intro SQL Window Functions