Aggregate functions in BigQuery are used to perform calculations on multiple rows of data and return a single result. These functions are useful in summarizing or aggregating data for reporting, analytics, or further processing.
COUNT()
- Explanation: The COUNT() function returns the total number of rows in a dataset that match a specified condition. If no condition is specified, it counts all rows.
- Usage:
- COUNT(*): Counts all rows in the dataset, including those with NULL values.
- COUNT(column_name): Counts the number of non-NULL values in the specified column.
- Example:
SELECT COUNT(*) AS total_rows
FROM orders;
This will return the total number of rows in the orders table.
- Conditional Counting:
You can add conditions to count only rows that match a specific condition, such as:
SELECT COUNT(order_id) AS completed_orders
FROM orders
WHERE status = 'completed';
This will return the number of orders with a "completed" status.
SUM()
- Explanation: The SUM() function adds up the values in a numeric column for all the rows that match a specific condition (if any). It ignores NULL values.
- Usage:
- SUM(column_name): Sums up the values in the specified column.
- Example:
SELECT SUM(total_amount) AS total_sales
FROM orders;
This query calculates the total sales from all the orders.
- Conditional Summing:
You can use it with a WHERE clause to sum up values that meet a specific condition, such as summing the sales only from completed orders:
SELECT SUM(total_amount) AS total_completed_sales
FROM orders
WHERE status = 'completed';
AVG()
- Explanation: The AVG() function calculates the average (mean) of the values in a numeric column. It ignores NULL values.
- Usage:
- AVG(column_name): Returns the average of the values in the specified column.
- Example:
SELECT AVG(total_amount) AS average_order_value
FROM orders;
This query calculates the average order value for all the orders.
- Conditional Average:
You can calculate the average for specific rows by using a condition, such as:
SELECT AVG(total_amount) AS average_completed_order_value
FROM orders
WHERE status = 'completed';
MAX()
- Explanation: The MAX() function returns the largest (maximum) value in a column. It can be applied to numeric, date, or string columns. For strings, it returns the lexicographically greatest value.
- Usage:
- MAX(column_name): Returns the maximum value in the specified column.
- Example:
SELECT MAX(total_amount) AS largest_order_value
FROM orders;
This query returns the value of the largest order in terms of total_amount.
- String Example:
For string-based columns, the function returns the lexicographically last value:
SELECT MAX(customer_name) AS last_customer
FROM orders;
In this case, it would return the last customer name in alphabetical order.
MIN()
- Explanation: The MIN() function returns the smallest (minimum) value in a column. Like MAX(), it can also be applied to numeric, date, or string columns. For strings, it returns the lexicographically smallest value.
- Usage:
- MIN(column_name): Returns the minimum value in the specified column.
- Example:
SELECT MIN(total_amount) AS smallest_order_value
FROM orders;
This query returns the value of the smallest order in terms of total_amount.
- Date Example:
To find the earliest date in a column:
SELECT MIN(order_date) AS earliest_order
FROM orders;
This will return the date of the earliest order.
- String Example:
Similar to MAX(), the MIN() function can also be applied to string columns:
SELECT MIN(customer_name) AS first_customer
FROM orders;
In this case, it returns the first customer name in alphabetical order.
Summary of Use Cases
These aggregate functions are essential for summarizing large datasets. They allow you to:
- Count rows based on specific conditions (e.g., total sales, customer segments).
- Sum numerical data to get totals (e.g., revenue, expenses).
- Calculate averages to understand trends (e.g., average sales value).
- Find maximum and minimum values to identify extremes (e.g., highest and lowest transaction amounts).
These are the fundamental building blocks in writing queries for reporting and data analysis. They are particularly powerful when combined with GROUP BY clauses to summarize data across different dimensions (e.g., totals per customer, sales by month).
