Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single scalar value.

Overview

Aggregate functions are used in conjunction with the GROUP BY clause, the HAVING clause, and the SELECT clause to summarize data. They can also be used without a GROUP BY clause to aggregate all rows in a table into a single summary row.

Common Aggregate Functions

Function Description Syntax Example
AVG() Calculates the average value of a numeric expression. Ignores NULL values. SELECT AVG(column_name) FROM table_name;
COUNT() Counts the number of rows in a group or the number of non-NULL values in a column. SELECT COUNT(column_name) FROM table_name;
SELECT COUNT(*) FROM table_name;
MAX() Returns the maximum value in a set of values. SELECT MAX(column_name) FROM table_name;
MIN() Returns the minimum value in a set of values. SELECT MIN(column_name) FROM table_name;
SUM() Calculates the sum of values in a numeric expression. Ignores NULL values. SELECT SUM(column_name) FROM table_name;
GROUP_CONCAT() Concatenates non-NULL values from a group into a single string. SELECT GROUP_CONCAT(column_name) FROM table_name GROUP BY group_column;
STDEV() / STDEVP() Calculates the standard deviation. STDEV for sample, STDEVP for population. SELECT STDEV(column_name) FROM table_name;
VAR() / VARP() Calculates the variance. VAR for sample, VARP for population. SELECT VAR(column_name) FROM table_name;

Using Aggregate Functions with GROUP BY

The GROUP BY clause groups rows that have the same values in specified columns into summary rows. Aggregate functions are then applied to each group.

Example:

Let's consider a table named Orders with columns CustomerID and OrderAmount. We want to find the total amount spent by each customer.


SELECT
    CustomerID,
    SUM(OrderAmount) AS TotalAmountSpent
FROM
    Orders
GROUP BY
    CustomerID;

Using Aggregate Functions with HAVING

The HAVING clause is used to filter groups based on a specified condition. It is typically used with aggregate functions.

Example:

Find customers who have spent more than $1000 in total.


SELECT
    CustomerID,
    SUM(OrderAmount) AS TotalAmountSpent
FROM
    Orders
GROUP BY
    CustomerID
HAVING
    SUM(OrderAmount) > 1000;

Handling NULL Values

Most aggregate functions, except for COUNT(*), ignore NULL values in the column they operate on. If a column contains only NULL values or if the group contains no rows, aggregate functions will typically return NULL (e.g., AVG, SUM, MAX, MIN). COUNT(column_name) will return 0 if there are no non-NULL values, and COUNT(*) will return 0 if there are no rows.

Important Note: COUNT(*) counts all rows, including those with NULL values in specific columns, whereas COUNT(column_name) only counts rows where column_name is not NULL.

Distinct Values with Aggregate Functions

The DISTINCT keyword can be used within an aggregate function to consider only unique values.

Example:

Count the number of unique customers who have placed orders.


SELECT
    COUNT(DISTINCT CustomerID) AS NumberOfUniqueCustomers
FROM
    Orders;

This page provides a foundational understanding of aggregate functions in SQL. For more advanced usage and specific database system implementations, please refer to the relevant documentation.