Aggregate function in SQL with Examples

by

In a Database Management System (DBMS), an aggregate function is a special tool that performs calculations on a set of values to return a single value. It simplifies data analysis by summarizing large volumes of data.

How Aggregate function works?

  • Collects Data: The first step involves gathering the data from one or more columns in a database. This could include various types of data, such as numerical, date, or text, depending on the specific function and the data it is applied to.
  • Applies a Rule: Once the data is collected, the aggregate function applies a predetermined rule or calculation to these values. This could involve arithmetic operations like addition or finding averages, or other types of calculations like counting or identifying maximum and minimum values.
  • Gives a Result: The end result of an aggregate function is a single value that offers insight into the collected data. This result could be a total, an average, a count of items, or the highest/lowest value among the collected data, among other outcomes.

Common Aggregate Functions

1. SUM

Adds together all the numerical values in a specified column.

Example: To find out the total amount from all orders, you would use the SUM function on the OrderAmount column.

SQL Query

SELECT SUM(OrderAmount) FROM Orders;

How it works: If the OrderAmount values are 10, 20, and 30, SUM adds them up to get a total of 60.

2. AVG

Calculates the average of numerical values in a column.

Example: To calculate the average amount per order, AVG is used on the OrderAmount column.

SQL Query

SELECT AVG(OrderAmount) FROM Orders;

How it works: Using the same OrderAmount values (10, 20, 30), AVG calculates the average by adding the numbers (60) and dividing by the count of values (3), resulting in an average of 20.

3. COUNT

Tallies the total number of entries in a column, which can be particularly useful for understanding the size of datasets.

Example: To count the number of orders made, you would apply COUNT to the OrderID column.

SQL Query

SELECT COUNT(OrderID) FROM Orders;

How it works: If there are 3 orders, COUNT(OrderID) returns 3, indicating there are three entries in the Orders table.

4. MAX/MIN

Identifies the maximum or minimum value within a set of values in a column, useful for understanding the range or distribution of data.

MAX Example

To find the highest order amount, MAX is used on the OrderAmount column.

SQL Query for MAX

SELECT MAX(OrderAmount) FROM Orders;

How it works (MAX): Given OrderAmount values of 10, 20, and 30, MAX identifies 30 as the highest amount.

MIN Example

Conversely, to find the lowest order amount, MIN is used on the same column.

SQL Query for MIN

SELECT MIN(OrderAmount) FROM Orders;

How it works (MIN): With the same set of values, MIN identifies 10 as the lowest amount.