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.