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

SELECTAvg(price)ASAveragePrice 
FROMfruits;

Output:

1.24

2. Count Function: COUNT()

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

Count Function Example

SELECTCount(*)ASFruitsWithInventoryGreaterThan50 
FROMfruits
WHEREinventory>=50;

Output:

3

3. Maximum Function: MAX() 

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

Maximum Function Example

SELECTMax(price)ASMaximumPrice 
FROMfruits;

Output:

5

4. Minimum Function: MIN()

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

Minimum Function Example

SELECTMin(inventory)ASMinimumInventory 
FROMfruits;

Output:

15

5. Sum Function: SUM()

The Sum Function calculates the sum of a specified column.

Sum Function Example

SELECTSum(inventory)ASTotalInventory 
FROMfruits;

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

SELECTGroup_concat(`product name`SEPARATOR', ')ASFruits 
FROMfruits;

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

SELECTStd(price)ASStandardDeviation
FROMfruits;

Output:

1.80052309609941

8. Variance Function: VAR()

The variance function calculates the variance of a specified column.

Variance Function Example

SELECTVar(inventory)ASVariance
FROMfruits;

Output:

1616

9. Median Function: MEDIAN()

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

Median Function Example

SELECTAvg(inventory)ASmedianinventory
FROM(
SELECTinventory
FROMfruits
ORDERBYinventorylimit2-
(
SELECTcount(*)
FROMfruits) % 2offset
(
SELECT(count(*)-1)/2
FROMfruits))subquery;

Output:

50

10. First Function: FIRST()

This function the first value in a specified column.

First Function Example

SELECTfirst_value(`productname`)OVER(ORDERBY`productNAME`)ASfirstfruit
FROMfruits;

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.