IT2EDU Empowering Education Through Technology
Aggregate functions in SQL are used to perform calculations on a set of values and return a single result. The most commonly used aggregate functions are SUM(), AVG(), COUNT(), MAX(), and MIN(). These functions can be used to perform various mathematical operations on a group of values, such as calculating the sum, average, count, maximum or minimum value of a specific column.
For example, consider a table named “sales” with the following data:
| id | product | quantity | price |
|---|---|---|---|
| 1 | Apple | 10 | 2.50 |
| 2 | Orange | 15 | 1.75 |
| 3 | Banana | 8 | 3.00 |
| 4 | Mango | 20 | 2.00 |
| 5 | Grape | 12 | 2.50 |
Using aggregate functions, we can perform the following operations:
SUM() SQL Aggregate Function
The SUM() SQL Aggregate function Calculates the total value of a column.
SELECT SUM(quantity) as total_quantity FROM sales;
Output:
total_quantity
65
This query calculates the total quantity of all products sold, which is 65.
AVG() SQL Aggregate Function
The AVG() SQL Aggregate function Calculates the average value of a column.
SELECT AVG(price) as average_price FROM sales;
Output:
average_price
2.15
This query calculates the average price of all products sold, which is 2.15.
COUNT() SQL Aggregate Function
The COUNT() is an Aggregate function in SQL, Calculates the number of rows in a table or the number of non-null values in a column.
SELECT COUNT(*) as total_rows FROM sales;
total_rows
5
This query calculates the total number of rows in the “sales” table, which is 5.
MAX() SQL Aggregate Function
The MAX() is an Aggregate function in SQL which returns the maximum value of a column.
SELECT MAX(price) as max_price FROM sales;
Output:
max_price
3.00
This query returns the maximum price of any product sold, which is 3.00.
MIN() SQL Aggregate Function
The MIN() is an Aggregate function in SQL which Returns the minimum value of a column.
SELECT MIN(price) as min_price FROM sales;
Output:
min_price
1.75
This query returns the minimum price of any product sold, which is 1.75.
GROUP BY:
The GROUP BY is an Aggregate function in SQL, The function Groups the result set by one or more columns.
SELECT product, SUM(quantity) as total_quantity FROM sales GROUP BY product;
Output:
| product | total_quantity |
|---|---|
| Apple | 10 |
| Banana | 8 |
| Grape | 12 |
| Mango | 20 |
| Orange | 15 |
This query groups the sales by product and calculates the total quantity sold for each product.
Conclusion:
In conclusion, aggregate functions in SQL are useful for performing calculations on a set of values and returning a single result. The most commonly used functions are SUM(), AVG(), COUNT(), MAX(), and MIN(), which can be used to perform mathematical operations on a group of values. Additionally, GROUP BY can be used to group the result set by one or more columns. By using these functions and clauses in SQL queries, we can easily retrieve useful information from large sets of data.
