Aggregation Functions — COUNT, SUM, AVG, MIN, MAX

Counting Rows with COUNT

The COUNT function acts as a tallying mechanism that scans a defined set of rows and returns the total number of non-null occurrences. Unlike other aggregate functions that require numeric input, COUNT can be used on any data type. When you pass a specific column name to COUNT, the engine evaluates the presence of data in that field, effectively ignoring NULL values. However, using COUNT(*) instructs the database to count all rows regardless of the content in individual columns, as it treats the row as a single unit of record. This distinction is vital for accurate reporting: if a column contains missing data points, COUNT(column_name) will yield a smaller result than COUNT(*) for the same dataset. Understanding this behavior allows you to accurately measure population size versus the availability of specific data attributes within your database schema.

-- Counts all rows in the orders table
SELECT COUNT(*) AS total_orders FROM orders;
-- Counts only rows where the shipping_date is not NULL
SELECT COUNT(shipping_date) AS shipped_orders FROM orders;

Calculating Totals with SUM

The SUM function is specifically designed to perform additive calculations on sets of numerical data. When the SQL engine executes a SUM operation, it traverses the specified column, identifies every non-null numerical value, and computes their cumulative total. If the engine encounters a NULL value, it ignores that entry entirely, which prevents the accidental contamination of your results by missing data placeholders. Because this function relies on arithmetic addition, it will throw an error if you attempt to apply it to character strings or non-numeric types. The power of SUM lies in its ability to condense thousands of individual transaction amounts into a single balance. This logic is fundamental for financial reporting, inventory valuation, and any scenario where the magnitude of cumulative resources is the primary objective of your analytical query.

-- Calculates the total revenue across all sales
SELECT SUM(price) AS total_revenue 
FROM sales_data;
-- Sums only the revenue for a specific category
SELECT SUM(price) AS electronics_revenue 
FROM sales_data 
WHERE category = 'Electronics';

Finding Averages with AVG

AVG calculates the mean value by dividing the sum of all non-null entries in a column by the count of those entries. It is a composite function, effectively performing both SUM and COUNT operations internally to derive the central tendency of a dataset. Just like SUM, it treats NULL values as non-existent rather than as zeroes; this is a critical distinction, as including zeroes in a denominator would mathematically deflate the average. By automatically excluding NULLs, the function ensures that the average reflects only the active data points present in your records. This is highly beneficial when dealing with incomplete survey data or sparse sensor inputs where missing readings should not count as neutral values. Using AVG allows you to establish performance benchmarks, identify typical transaction sizes, and normalize large sets of information into comparable metrics.

-- Calculates the average order value
SELECT AVG(order_total) AS average_sale_value 
FROM orders;
-- Calculates average price while ignoring NULL values
SELECT AVG(price) AS avg_item_price 
FROM inventory;

Identifying Extremes with MIN and MAX

MIN and MAX are comparison operators that scan a column to find the lowest and highest values, respectively. These functions work across various data types, including numbers, dates, and even strings, where they rely on alphabetical sorting logic. When processing a column, the engine compares every element to the current minimum or maximum candidate, updating its internal 'best match' until the scan is complete. These functions are indispensable for temporal analysis, such as identifying the earliest or most recent timestamp in an activity log. Because they require a complete scan of the data unless an index is present, their performance is tied to the underlying database structure. By mastering MIN and MAX, you gain the ability to pinpoint outliers, determine the lifespan of records, and define the boundaries of your datasets without needing complex sorting or limiting operations.

-- Finds the earliest and latest order dates
SELECT MIN(order_date) AS first_order, MAX(order_date) AS last_order 
FROM orders;
-- Finds the cheapest and most expensive product
SELECT MIN(price), MAX(price) 
FROM inventory;

Combining Aggregates for Insights

In real-world applications, you rarely rely on a single aggregate function; instead, you combine them to build a comprehensive snapshot of your data. Because all these functions reduce a set to a single scalar value, they can be utilized in the same SELECT statement to generate multi-dimensional summaries simultaneously. This process is efficient because the engine often computes these values during a single pass through the requested data rows. When you combine functions like COUNT, SUM, and AVG, you are essentially creating a report template that summarizes volume, value, and trend in one step. This capability is the foundation of data exploration, allowing you to derive sophisticated insights such as 'number of transactions,' 'total revenue,' and 'average price' in a single query. Mastering this multi-functional approach transforms raw tables into actionable business intelligence dashboards.

-- Combining multiple aggregates in one report
SELECT 
    COUNT(*) AS total_sales, 
    SUM(amount) AS gross_revenue, 
    AVG(amount) AS average_per_sale,
    MIN(amount) AS smallest_sale
FROM transactions;

Interview questions

All SQL interview questions →

Check yourself

Take the full SQL quiz →