SQL Aggregate Functions

SQL Aggregate Functions are used to calculate a set of values and return a single value. These functions are often used with the SELECT statement in SQL. These are several examples of aggregate functions in SQL:

Examples

COUNT

The COUNT function counts the number of rows in a table or that match a specific condition.

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

For the “orders” table:

If we want to count the number of orders, we can use the COUNT function:

SELECT COUNT(order_id)
FROM orders;

This will return the result:

COUNT(order_id)

5

If we want to count the number of orders for a specific customer, we can use the COUNT function with a WHERE clause:

SELECT COUNT(order_id)
FROM orders
WHERE customer_id = 1;

This will return the result:

COUNT(order_id)

2

SUM

The SUM function calculates the sum of a set of values.

SELECT SUM(column_name)
FROM table_name
WHERE condition;

If we want to find the total amount of all orders, we can use the SUM function:

SELECT SUM(amount)
FROM orders;

This will return the result:

SUM(amount)

825

AVG

The AVG function calculates the average of a set of values.

SELECT AVG(column_name)
FROM table_name
WHERE condition;

If we want to find the average amount of all orders, we can use the AVG function:

SELECT AVG(amount)
FROM orders;

This will return the result:

AVG(amount)

165

MIN

The MIN function finds the minimum value in a set of values.

SELECT MIN(column_name)
FROM table_name
WHERE condition;

If we want to find the minimum amount of all orders, we can use the MIN function:

SELECT MIN(amount)
FROM orders;

This will return the result:

MIN(amount)

75

MAX

The MAX function finds the maximum value in a set of values.

SELECT MAX(column_name)
FROM table_name
WHERE condition;

If we want to find the maximum amount of all orders, we can use the MAX function:

SELECT MAX(amount)
FROM orders;

This will return the result:

MAX(amount)

300

Further Readings

1. SQL Aggregate Functions - W3Schools
2. Aggregate Functions - SQL Tutorial
3. SQL Aggregate Functions: A Beginner’s Guide - Database Star
4. Aggregate Functions in MySQL - MySQL Tutorial
5. SQL Server Aggregate Functions - SQL Server Tutorial
6. Aggregate Functions (Transact-SQL) - Microsoft Docs