# MySQL Aggregate Functions

## MySQL AVG() Function

Main Article :- MySql AVG() Function

The ** AVG()** method returns the

**average value of an expression**.

Return the average of the **distinct values of expr** with the ** DISTINCT** option.

**Note:** NULL values are not taken into consideration.

### Syntax

Here is the syntax of MySQL AVG() function:

AVG(expr)

**Parameter Values**

Parameter | Description |
---|---|

expr |
Required. A expression. |

**Technical Details**

Works in: | From MySQL 4.0 |
---|

### Examples:-

**Example 1:** In the "EMPLOYEESIMPLE" table, get the **average value** for the "Salary" column:

Let's take this **EMPLOYEESIMPLE** table to execute the function:

EmployeeName | Salary |
---|---|

Pandurengan | 32000 |

Devi Mai | 21000 |

The query will be,

SELECT AVG(Salary) AS AverageSalary FROM EMPLOYEESIMPLE;

**Result:**

AverageSalary |
---|

26500 |

From the example, we took the "EmployeeSimple" table to find the **average of the "Salary" column**, so the average salary is **26500**.

**Example 2:** Pick albums that are **more costly than the average**:

Let's took **"BOOKSIMPLE"** table to execute the following query:

ID | BookName | Price |
---|---|---|

1 | Making Sense Of SQL | 123.45 |

2 | Oracle 11g PL/SQL Programming | 199.97 |

The query will be look like this,

SELECT * FROM BOOKSIMPLE
WHERE Price > (SELECT AVG(Price) FROM BOOKSIMPLE);

**Result:**

Id | BookName | Price |
---|---|---|

2 | Oracle 11g PL/SQL Programming | 199.97 |

From the result, the actual **average value is 161.70**. According to the requirements, we need to get **more costly than average**, so the **output is 199.7**.

## MySQL COUNT() Function

Main Article :- MySql COUNT() Function

The MySQL ** count()** method returns an

**expression's count**. It enables us to count all or just a subset of the table's rows that

**meet a set of criteria**.

** BIGINT** is the return type of this type of

**aggregate function**. If there are no rows that match, this function

**returns 0**.

**Note:** The calculation excludes the number of **NULL** values.

### Syntax:

Here is the syntax of MySQL COUNT() function:

COUNT(expr)

**Parameter Values**

Parameter | Description |
---|---|

expr |
Required. A field or a string value. |

**Technical Details**

Works in: | From MySQL 4.0 |
---|

### Examples:-

**Example:** The **number of Books in the "BookName" column** should be returned:

Let's take **"BOOKSIMPLE"** to execute the example.

Name | Price |
---|---|

Database Management | 65 |

Professional Oracle | 95 |

PHP And MySQL Bible | 145 |

Learing Oracle SQL & Pl/sql | 110 |

The query will be like this,

SELECT COUNT(BookName) AS NumberOfBooks FROM BOOKSIMPLE;

**Result:**

NumberOfBooks |
---|

4 |

The result is to **count the number of books** in the BookName column.

## MySQL MAX() Function

Main Article :- MySql MAX() Function

In a list of values, the ** MAX()** method returns the

**maximum value**.

### Syntax:

Here is the syntax of MySQL MAX() function:

MAX(expr)

**Parameter Values**

Parameter | Description |
---|---|

expr |
Required. The expression to calculate. |

**Technical Details**

Works in: | From MySQL 4.0 |
---|

### Examples:-

**Example:** Let us find out the **maximum salary** paid to any employee:

Let's take **EMPLOYEESIMPLE** to execute the following query:

EmployeeName | Salary |
---|---|

Hanumanthan | 21000 |

Harish Karthik | 37500 |

Chandra | 17000 |

Ramanathan | 15000 |

The query will be like this,

SELECT MAX(SALARY) AS MaximumSalary
FROM EMPLOYEESIMPLE;

**Result:**

MaximumSalary |
---|

37500 |

As you can see, the output tells us the maximum value in the Salary column, which is the **highest amount paid as salary**.

## MySQL MIN() Function

Main Article :- MySql MIN() Function

In a list of values, the ** MIN()** method returns the

**maximum value**.

### Syntax:

Here is the syntax of MySQL MIN() function:

MIN(expr)

**Parameter Values**

Parameter | Description |
---|---|

expr |
Required. The expression to calculate. |

**Technical Details**

Works in: | From MySQL 4.0 |
---|

### Examples:-

**Example:** Let us find out the **minimum salary** paid to any employee:

Let's take **EMPLOYEESIMPLE** to execute the following query:

EmployeeName | Salary |
---|---|

Hanumanthan | 21000 |

Harish Karthik | 37500 |

Chandra | 17000 |

Ramanathan | 15000 |

The query will be like this,

SELECT MIN(SALARY) AS MinimumSalary
FROM EMPLOYEESIMPLE;

**Result:**

MinimumSalary |
---|

15000 |

As you can see, the output tells us the minimum value in the Salary column, which is the **minimum amount paid as salary**.

## MySQL SUM() Function

Main Article :- MySql SUM() Function

** SUM() adds up all of the values**.

**Note: NULL** values are not allowed.

### Syntax:

Here is the syntax of MySQL SUM() function:

SUM(expr)

**Parameter Values**

Parameter | Description |
---|---|

expr |
Required. A expression. |

**Technical Details**

Works in: | From MySQL 4.0 |
---|

### Examples:-

**Example:** The example to find the **total of the "Salary"** column is as follows:

Let's take **EMPLOYEESIMPLE** table to execute following query:

Name | Salary |
---|---|

Chandra | 10000 |

Dharan | 33500 |

Geetha | 21000 |

Hari Krishnan | 13500 |

The query will be like this,

SELECT SUM(Salary) AS TotalAmountOfSalary FROM EMPLOYEESIMPLE;

**Result:**

TotalAmountOfSalary |
---|

90500 |

The **sum of salary column is 90500**.