Aggregate Functions in DBMS

Last Updated on April 17, 2023 by Prepbytes

Aggregate Functions in DBMS are an essential component that helps the user in the analysis and extraction of meaningful information from large datasets. The Aggregate Functions in DBMS perform the calculation over the whole data and return a single-valued output. Here we will learn about Aggregate Functions in DBMS and different types of Aggregate Functions in DBMS.

Aggregate Functions in DBMS

Aggregate functions in DBMS are used to perform calculations on sets of data. They take a set of values as input and return a single value as output. These functions are often used to generate summary statistics on large datasets, such as the average, minimum, maximum, and sum of a set of values. They can also be used to count the number of rows in a dataset, and perform other complex calculations. In a nutshell, Aggregate Functions in DBMS are used for summarizing the data.

Types of Aggregate Functions in DBMS

Aggregate Functions in DBMS are of different types as shown in the figure given below.

To understand the above-mentioned Aggregate Functions in DBMS, let us consider the following table.

Table Name: PREP_TABLE

PRODUCT	COMPANY	QTY	RATE	COST
Product1	Company1	2	10	20
Product2	Company2	3	25	75
Product3	Company1	2	30	60
Product4	Company3	5	10	50
Product5	Company2	2	20	40
Product6	Company1	3	25	75
Product7	Company1	5	30	150
Product8	Company1	3	10	30
Product9	Company2	2	25	50
Product10	Company3	4	30	120

COUNT()

This COUNT() function is used to count the number of rows in a table or a result set. It can also be used with a specific column to count the number of non-null values in that column.

Syntax of COUNT() Function

COUNT(*) OR COUNT(COLUMN_NAME)

Example of COUNT() Function

SQL Query:
SELECT COUNT(*)  
FROM PREP_TABLE;  

Output:

10

Example of COUNT() Function with WHERE Clause

SQL Query:
SELECT COUNT(*)  
FROM PREP_TABLE;  
WHERE RATE>=20;

Output:

7

Example of COUNT() Function with DISTINCT

SQL Query:
SELECT COUNT(DISTINCT COMPANY)  
FROM PREP_TABLE;  

Output:

3

Example of COUNT() Function with GROUP BY

SQL Query:
SELECT COMPANY, COUNT(*)  
FROM PREP_TABLE  
GROUP BY COMPANY; 

Output:

Company1  5
Company2  3
Company3  2

Example of COUNT() Function with HAVING

SQL Query:
SELECT COMPANY, COUNT(*)  
FROM PREP_TABLE  
GROUP BY COMPANY  
HAVING COUNT(*)>2; 

Output:

Company1  5 
Company2  3

SUM()

The SUM() function in DBMS accepts a column name as an input and returns the total of all non-NULL values in that column. It only works on numeric fields (i.e the columns contain only numeric values). If this function is applied to columns that include both non-numeric (like, strings) and numeric values, it only considers the numeric values. If there are no numeric values, the method returns 0.

Syntax of SUM() Function

SUM(COLUMN_NAME) 

Example of SUM() Function

SQL Query:
SELECT SUM(COST)  
FROM PREP_TABLE;

Output:

670

Example of SUM() Function with WHERE

SQL Query:
SELECT SUM(COST)  
FROM PREP_TABLE 
WHERE QTY>3;  

Output:

320

Example of SUM() Function with GROUP BY

SQL Query:
SELECT SUM(COST)  
FROM PREP_TABLE 
WHERE QTY>3  
GROUP BY COMPANY;  

Output:

Company1  150
Company2  170

Example of SUM() Function with HAVING

SQL Query:
SELECT COMPANY, SUM(COST)  
FROM PREP_TABLE 
GROUP BY COMPANY  
HAVING SUM(COST)>=170;  

Output:

Company1 335
Company3 170

AVG()

The AVG() aggregate function in DBMS takes the column name as an input and returns the average of all non-NULL values in that column. It only works on numeric fields (i.e the columns contain only numeric values).

Syntax of AVG() Function

AVG(COLUMN_NAME)

Example of AVG() Function

SQL Query:
SELECT AVG(COST)  
FROM PREP_TABLE;  

Output:

67.00

MAX()

The MAX() function accepts the column name as a parameter and returns the maximum value in the column. When no row is specified, MAX() function returns NULL.

Syntax of MAX() Function

MAX(COLUMN_NAME)

Example of MAX() Function

SQL Query:
SELECT MAX(RATE)  
FROM PREP_TABLE;  

Output:

30

MIN()

The MIN() function accepts the column name as a parameter and returns the minimum value in the column. When no row is specified, MIN() Function returns NULL as result.

Syntax of MIN() Function:

MIN(COLUMN_NAME)

Example of MIN() Function

SQL Query:
SELECT MIN(RATE)  
FROM PREP_TABLE;  

Output:

10

Conclusion
In this article, we learned about the Aggregate Functions in DBMS. The Aggregate Functions in DBMS help us in dealing with large datasets. We have discussed different types of Aggregate Functions in DBMS which include, COUNT(), SUM(), AVG(), MAX(), and MIN(). Whether you are a data analyst, a database administrator, or a developer, understanding how to use aggregate functions in DBMS is essential for working with large datasets and making informed decisions based on data analysis.

Frequently Asked Questions (FAQs)

Here are some Frequently Asked Questions related to “Aggregate Functions in DBMS”.

Ques 1. What is the purpose of using aggregate functions in DBMS?
Ans. The purpose of using aggregate functions in DBMS is to summarize data from one or more columns of a table and provide meaningful insights.

Ques 2. What are the commonly used aggregate functions in DBMS?
Ans. Some commonly used aggregate functions in DBMS are COUNT(), SUM(), AVG(), MAX(), MIN(), etc.

Ques 3. What is the difference between SUM() and AVG() aggregate functions in DBMS?
Ans. SUM() function calculates the total sum of a column whereas the AVG() function calculates the average of a column.

Ques 4. What is the difference between the MAX() and MIN() aggregate functions in DBMS?
Ans. MAX() function returns the maximum value in a column whereas MIN() function returns the minimum value in a column.

Ques 5. Can we use aggregate functions in DBMS with the DISTINCT keyword?
Ans. Yes, aggregate functions can be used with the DISTINCT keyword to perform calculations on unique values of a column.