Functions in MySQL

Aggregate - Constituting or amounting to a whole

Aggregate functions describe operations on a set of values, like as counting, averaging, or finding minimum or maximum values.aggregate functions to assist with the restating of large volumes of data. The aggregate functions count, sum, avg, max, min and list not handle NULL in the same way as ordinary functions and operators.  Aggregate functions perform a calculation on a set of values and return a single value. 

Aggregate functions  are used in the act of joining with a GROUP BY clause to dispose values from a result set into groups.

There are some functions that operate on sets of values.

• MIN() and MAX() : find smallest and largest values.
• SUM() :  summarize numeric values to produce sums (totals) .
• COUNT() : counts rows, values, or the number of distinct values.
• AVG() : calculate the average,used with numeric values.

Aggregate functions may be used with or without a GROUP BY clause that places rows into groups. Without a GROUP BY clause, an aggregate function calculates a summary value based on the entire set of selected rows.  MySQL behaves with all the rows as a single group. With a GROUP BY clause, an aggregate function calculates a summary value for each group. 

MAX() and MIN( ) : MAX function returns the maximum and MIN function returns the minimum value of the set of values in expression.

For Example : In the following example used a table 'clients' and used for max and min function on the salary column of the table.

mysql> SELECT designation, MIN(salary),
          MAX(salary) FROM clients GROUP BY designation;

SUM() : SUM function returns the sum of all values in an expression.

For Example : For the clients table we have apply the SUM function on the salary column and get total sum on salary.

mysql> select SUM(salary) FROM clients;

COUNT( ) : COUNT function returns the count of the items in expression. The COUNT() function can be used in several ways to count either rows or values.

For Example : It is used to find  how many clients , We have as follows.

mysql> select count(*) from clients;

AVG( ) : AVG is used to calculate average value of an expression. It ignores NULL values.

For Example : We can use AVG function to calculate the average salary of all clients buy executing the following query.
mysql> select avg<salary> from clients;