Basic Aggregate Functions

Which single query can reveal the total number of orders, the highest order value, and the average price of items sold in your database? SQL aggregate functions provide these answers by transforming individual records into meaningful summary statistics.

Beyond Rows: Why Summarize Your Data?

Raw data, while essential, often contains too much detail for quick analysis or decision-making. Imagine sifting through thousands of individual sales transactions just to find the total revenue for the month. This process would be time-consuming and prone to error.

Aggregate functions in SQL provide a powerful way to condense large datasets into concise, actionable summaries. They allow you to perform calculations across groups of rows, giving you a high-level view of your data's characteristics without needing to examine every single record. This summarization is critical for reporting, trend analysis, and making informed business decisions.

Aggregate Function

A function that performs a calculation on a set of rows and returns a single summary value.

Example: Calculating the total number of customers using COUNT(), or finding the average order value using AVG(order_amount) from a table of individual transactions.

Counting Records: The `COUNT()` Function

The COUNT() function is fundamental for understanding the volume of your data. It helps you determine how many rows exist in a table or how many non-NULL values are present in a specific column. There are three primary ways to use COUNT().

COUNT(): This counts all rows in the specified table or result set, including rows with NULL values in any column. It is the most common way to get a total row count.
COUNT(column_name): This counts only the non-NULL values in the specified column. Rows where column_name is NULL are excluded from the count.
COUNT(DISTINCT column_name): This counts the number of unique, non-NULL values in the specified column. Duplicate values and NULLs are ignored.

sqlCounting All Rows with `COUNT(*)`

sqlCounting Non-NULL and Distinct Values

Check Your Understanding

Given a table products with 5 rows, where the description column has 2 NULLs and the category column has values 'Electronics', 'Books', 'Electronics', 'Clothing', NULL, what is the result of COUNT(description) and COUNT(DISTINCT category) respectively?

Calculating Totals and Averages: `SUM()` and `AVG()`

When you need to understand the collective magnitude or central tendency of numerical data, SUM() and AVG() are indispensable. These functions operate exclusively on numeric columns, returning a single value that summarizes the entire set.

SUM(column_name): Calculates the total sum of all non-NULL values in the specified numeric column. If all values in the column are NULL, SUM() returns NULL.
AVG(column_name): Computes the average (arithmetic mean) of all non-NULL values in the specified numeric column. It is equivalent to SUM(column_name) / COUNT(column_name). Like SUM(), if all values are NULL, AVG() returns NULL.

sqlCalculating the Sum of Order Amounts

sqlCalculating the Average Order Amount

Check Your Understanding

If a transactions table has amounts 10, 20, NULL, 30, what will AVG(amount) return?

Finding Extremes: `MIN()` and `MAX()`

To identify the boundaries or range of your data, MIN() and MAX() are the go-to functions. They retrieve the smallest and largest values, respectively, from a specified column. These functions are versatile, working not only with numerical data but also with string and date/time data types.

MIN(column_name): Returns the smallest non-NULL value in the specified column. For strings, this is the first value in alphabetical order. For dates, it's the earliest date.
MAX(column_name): Returns the largest non-NULL value in the specified column. For strings, this is the last value in alphabetical order. For dates, it's the latest date.

Both MIN() and MAX() ignore NULL values. If all values in the column are NULL, they return NULL.

sqlFinding Minimum and Maximum Values

Try It Yourself

Write a single SQL query to find the total number of orders, the sum of all order amounts, the average order amount, the minimum order amount, and the maximum order amount from the orders table. Label the columns clearly.

sql

Key Takeaways

SQL aggregate functions summarize data from multiple rows into a single result, providing high-level insights.
COUNT() counts all rows, while COUNT(column) counts non-NULL values in a specific column, and COUNT(DISTINCT column) counts unique non-NULL values.
SUM(column) calculates the total of numerical values, and AVG(column) computes their arithmetic mean, both ignoring NULLs.
MIN(column) and MAX(column) find the smallest and largest non-NULL values, respectively, working on numerical, string, and date types.
These functions are powerful tools for data analysis, enabling you to answer complex questions about your data's characteristics with a single, concise query.

Basic Aggregate Functions

Beyond Rows: Why Summarize Your Data?

Counting Records: The COUNT() Function

Calculating Totals and Averages: SUM() and AVG()

Finding Extremes: MIN() and MAX()

Ready to keep this from fading?

Counting Records: The `COUNT()` Function

Calculating Totals and Averages: `SUM()` and `AVG()`

Finding Extremes: `MIN()` and `MAX()`