MySQL Aggregate Functions

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

Technical Details

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:

The query will be,

SELECT AVG(Salary) AS AverageSalary FROM EMPLOYEESIMPLE;

Result:

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:

The query will be look like this,

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

Result:

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.

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

Technical Details

Examples:-

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

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

The query will be like this,

SELECT COUNT(BookName) AS NumberOfBooks FROM BOOKSIMPLE;

Result:

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

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

Technical Details

Examples:-

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

Let's take EMPLOYEESIMPLE to execute the following query:

The query will be like this,

SELECT MAX(SALARY) AS MaximumSalary 
FROM EMPLOYEESIMPLE;

Result:

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

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

Technical Details

Examples:-

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

Let's take EMPLOYEESIMPLE to execute the following query:

The query will be like this,

SELECT MIN(SALARY) AS MinimumSalary 
FROM EMPLOYEESIMPLE;

Result:

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

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

Technical Details

Examples:-

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

Let's take EMPLOYEESIMPLE table to execute following query:

The query will be like this,

SELECT SUM(Salary) AS TotalAmountOfSalary FROM EMPLOYEESIMPLE;

Result:

The sum of salary column is 90500.