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.