What are Aggregate SQL Functions? (10 Definitions + Examples)

In the world of SQL, aggregate functions play a vital role in summarizing large amounts of data and performing powerful calculations. Whether you're new to SQL or simply looking to refresh your knowledge, understanding what aggregate functions are and how to use them can vastly improve your data strategy and make your everyday work easier. In this post, we'll provide a comprehensive definition of SQL aggregate functions and then share ten examples of the most commonly used ones.

Keep this post handy as a reference sheet for recalling these functions, analyzing key data points, and extracting valuable insights in a timely and efficient manner.

What are Aggregate SQL Functions?

An aggregate function is an SQL function that returns a single value calculated from a set of values. These values are typically those in a column or group of columns meeting certain conditions.

Before we dive into a slew of different aggregate functions, let's look at a simple example.

Aggregate SQL Function Example

Let's say we have a table named `sales` with columns `Product` and `Revenue`. If we wanted to calculate the total revenue for each product, we could use the SUM aggregate function, which looks like this:

SELECT product,
       Sum(revenue) AS Total_Revenue
FROM   sales
GROUP  BY product; 

This SQL statement will group the sales table by product and then calculate the total revenue for each product using the SUM aggregate function. The result will return a table with two columns: `Product` and `Total_Revenue`.

Now that we've covered this example, let's review a comprehensive list of aggregate SQL functions.

Aggregate SQL Functions

The functions in this section will be based on the following database:

Database: Fruits

1. Average Function: AVG()

The average function calculates the average value of a specified column.

Average Function Example

SELECT Avg(price) AS AveragePrice 
FROM   fruits; 

Output:

1.24

2. Count Function: COUNT()

This function counts the number of rows that match a specified condition.

Count Function Example

SELECT Count(*) AS FruitsWithInventoryGreaterThan50 
FROM   fruits
WHERE  inventory >= 50; 

Output:

3

3. Maximum Function: MAX() 

The max function returns the maximum value in a specified column.

Maximum Function Example

SELECT Max(price) AS MaximumPrice 
FROM   fruits; 

Output:

5

4. Minimum Function: MIN()

The min function returns the minimum value in a specified column.

Minimum Function Example

SELECT Min(inventory) AS MinimumInventory 
FROM   fruits; 

Output:

15

5. Sum Function: SUM()

The Sum Function calculates the sum of a specified column.

Sum Function Example

SELECT Sum(inventory) AS TotalInventory 
FROM   fruits; 

Output:

260

Note: For more math functions, check out our guide here. 

6. Group Concatenates Function: GROUP_CONCAT()

This function links the values of a column into a single string, separated by a specified delimiter.

Group Concatenates Function Example

SELECT Group_concat(`product name` SEPARATOR ', ') AS Fruits 
FROM   fruits; 

Output:

Apple, Orange, Banana, Pineapple, Watermelon

7. Standard Deviation Function: STD()

This SQL function calculates the standard deviation of a specified column.

Standard Deviation Function Example

SELECT Std(price) AS StandardDeviation
FROM   fruits; 

Output:

1.80052309609941

8. Variance Function: VAR()

The variance function calculates the variance of a specified column.

Variance Function Example

SELECT Var(inventory) AS Variance
FROM   fruits; 

Output:

1616

9. Median Function: MEDIAN()

The median function calculates the median value of a specified column.

Median Function Example

SELECT Avg(inventory) AS medianinventory
FROM   (
                SELECT   inventory
                FROM     fruits
                ORDER BY inventory limit 2 -
                         (
                                SELECT count(*)
                                FROM   fruits) % 2 offset
                         (
                                SELECT (count(*) - 1) / 2
                                FROM   fruits)) subquery;

Output:

50

10. First Function: FIRST()

This function the first value in a specified column.

First Function Example

SELECT   first_value(`product name`) OVER (ORDER BY `product NAME`) AS firstfruit
FROM     fruits;

Output:

Apple

Using SQL Aggregate Functions

Aggregate SQL functions are essential for performing calculations and summarizing data in a database. These functions allow you to retrieve valuable insights from your data by calculating averages, sums, counts, and more. Whether you're analyzing sales data, tracking customer behavior, or evaluating performance metrics, aggregate functions help you make informed decisions and uncover meaningful patterns in your data.

Author's Note: This post was written/edited by a human with the assistance of generative AI.