**Contents**show

## Aggregate Functions

Learn Aggregate Functions in MySQL

Till now you have learnt to work with functions that operate on individual rows in a table e.g., if you use Round( ) function then it will round off values from each row of the table.

MySQL also supports and provides group functions or aggregate functions. As you can make out that the group functions or aggregate functions work upon groups of rows, rather than on single rows. That is why, these functions are sometimes also called multiple row functions.

### Many group functions accept the following options :

DISTINCT

This option causes a group function to consider only distinct values of the argument expression.

ALL

This option causes a group function to consider all values including all duplicates.

The usage of these options will become clear with the coverage of examples in this section.

All the examples that we’ll be using here, shall be based upon following table empl.

#### Table Database table empl

EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |

8369 | SMITH | CLERK | 8902 | 1990-12-18 | 800.00 | NULL | 20 |

8499 | ANYA | SALESMAN | 8698 | 1991-02-20 | 1600.00 | 300.00 | 30 |

8521 | SETH | SALESMAN | 8698 | 1991-02-22 | 1250.00 | 500.00 | 30 |

8566 | MAHADEVAN | MANAGER | 8839 | 1991-04-02 | 2985.00 | NULL | 20 |

8654 | MOMIN | SALESMAN | 8698 | 1991-09-28 | 1250.00 | 1400.00 | 30 |

8698 | BINA | MANAGER | 8839 | 1991-05-01 | 2850.00 | NULL | 30 |

8839 | AMIR | PRESIDENT | NULL | 1991-11-18 | 5000.00 | NULL | 10 |

8844 | KULDEEP | SALESMAN | 8698 | 1991-09-08 | 1500.00 | 0.00 | 30 |

8882 | SHIAVNSH | MANAGER | 8839 | 1991-06-09 | 2450.00 | NULL | 10 |

8886 | ANOOP | CLERK | 8888 | 1993-01-12 | 1100.00 | NULL | 20 |

8888 | SCOTT | ANALYST | 8566 | 1992-12-09 | 3000.00 | NULL | 20 |

8900 | JATIN | CLERK | 8698 | 1991-12-03 | 950.00 | NULL | 30 |

8902 | FAKIR | ANALYST | 8566 | 1991-12-03 | 1300.00 | NULL | 20 |

8934 | MITA | CLERK | 8882 | 1992-01-23 | 1300.00 | NULL | 10 |

### 1. AVG

This function computes the average of given data.

Syntax :-

```
AVG([DISTINCT / ALL] n)
```

- Returns average value of parameter(s) n.

Argument type : Numeric Return value : Numeric

#### Example 1. Calculate average salary of all employees listed in table empl.

Solution.

```
mysql> SELECT AVG(sal) "Sverage"
from empl ;
```

Average |

2073.928571 |

1 row in set (0.01 sec)

### 2. COUNT

This function counts the number of rows in a given column or expression.

Syntax :

```
COUNT({ *[DISTINCT / ALL] expr})
```

- Returns the number of rows in the query.
- if you specify argument expr, this function returns rows where expr is not null. You can count either all rows, or only distinct values of expr.
- If you specify the asterisk (*), this function returns all rows, including duplicates and nulls.

Argument type : Numeric Return value : Numeric

#### Example 2. Count number of records in table empl.

Solution.

```
mysql> SELECT COUNT(*) "Total"
From empl ;
```

Total |

14 |

1 row in set (0.01 sec)

#### Example 3. Count number of jobs in table empl.

Solution.

```
mysql> SELECT COUNT(job) "Job Count"
From empl ;
```

Job Count |

14 |

1 row in set (0.01 sec)

#### Example 4. How many distinct jobs are listed in table empl ?

Solution.

```
mysql> SELECT COUNT(DISTINCT job) "Distinct Jobs"
From empl ;
```

Distinct Jobs |

15 |

1 row in set (0.01 sec)

### 3. MAX

This function returns the maximum value from a given column or expression.

Syntax :

```
MAX( [DISTINCT / ALL] expr)
```

Returns maximum value of argument expr.

Argument type : Numeric Return value : Numeric

#### Example 5. Display maximum salary from table empl.

Solution.

```
mysql> SELECT MAX(sal) "Maximum Salary"
From empl ;
```

Maximum Salary |

5000.00 |

1 row in set (0.01 sec)

### 4. MIN

This function returns the minimum value from a given column or expression.

Syntax :

```
MIN( [SIATINCT / ALL] expr)
```

- Returns minimum value of expr.

Argument type : Numeric Return value : Numeric

Example 6. Display the joining date of seniormost employee.

Solution.

```
mysql> SELECT MIN(hiredate) "Minimum Hire Date"
From empl ;
```

Minimum Hire Date |

1990-12-18 |

1 row in set (0.01 sec)

### 5. SUM

This function returns the sum of values in given column or expression.

Syntax :

```
SUM( [DISTINCT / ALL] n)
```

- Returns sum of values of n.

Argument type : Numeric Return value : Numeric

#### Example 7. Display total salary of all employees listed in table empl.

Solution.

```
mysql> SELECT SUM(sal) "Total Salary"
From empl ;
```

Total Salary |

29035.00 |

1 row in set (0.01 sec)

### Examples :

#### 1. To calculate the total gross for employees of grade ‘E2’,

the command is :

```
SELECT SUM(gross) FROM employee
WHERE grade = 'E2' ;
```

#### 2. To display the average gross of employees with grades ‘E1’ or ‘E2’, the command used is :

```
SELECT AVG(gross) FROM employee
WHERE (grade = 'E1' OR grade = 'E2') ;
```

#### 3. To count the number of employees in employee table, the SQL command is :

```
SELECT COUNT(*)
FROM employee ;
```

#### 4. To count the number of cities, the different members belong to, you use the following command :

```
SELECT COUNT(DISTINCT city) FROM members ;
```

Here the DISTINCT keyword ensures that multiple entries of the same city are ignored. The * is the only argument that includes NULLs when it is used only with COUNT, functions other than COUNT disregard NULLs in any case.

If you want to count the entries including repeats, the keyword ALL is used. The following command will COUNT the number of non NULL city fields in the member table

### Read More :-

### FAQ :-

#### Q 1. What are aggregate functions explain with examples?

Ans. **SQL** provides various aggregate functions which can summarize data of given table. This function is used to calculate number of rows in a table selected by query. COUNT returns the number of rows in the table when the column value is not NULL.

#### Q 2. What are the 6 aggregate functions of SQL?

Ans.

**SQL Aggregate Functions**

- AVG – calculates the average of a set of values.
- COUNT – counts rows in a specified table or view.
- MIN – gets the minimum value in a set of values.
- MAX – gets the maximum value in a set of values.
- SUM – calculates the sum of values.

#### Q 3. What is an example of an aggregate?

Ans. An **aggregate** is a collection of people who happen to be at the same place at the same time but who have no other connection to one another. **Example**: The people gathered in a restaurant on a particular evening are an **example of an aggregate**, not a group.

#### Q 4. Is sum an aggregate function?

Ans. The SQL Server **SUM**() **function** is an **aggregate function** that calculates the **sum** of all or distinct values in an expression. In this syntax: ALL instructs the **SUM**() **function** to return the **sum** of all values including duplicates. ALL is used by default.

#### Q 5. What is a aggregate symbol?

Ans. **Aggregate**. The **aggregate** is indicated by the **symbol** M if the candidate has met with all the requirements for an endorsement for university admission, and the **symbol** S in all other cases.