MYSQL FUNCTIONS

Aggregate Functions

Aggregate functions are also known as group functions. Aggregate functions return a result only in single row based on group of rows, rather than on single row. It always appears
in SELECT command and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement. Some of the aggregate functions are MIN, MAX, SUM, AVG, COUNT. The SQL aggregate functions return a single value, calculated from values in a column.
There are different types of aggregate functions

AVG()

This function returns the average value of a specified column.
Syntax

SELECT AVG(column_name) FROM table_name;

COUNT( )

This function returns the total number of values or rows of the specified field or column. COUNT (*) is a special function, as it returns the count of all rows in a specified table. It includes all the null and duplicate values.
Syntax
SELECT COUNT(*) FROM table_name;

DISTINCT Clause with COUNT( ) Function

The DISTINCT keyword helps us in removing the duplicate value from the result. When it is used with aggregate function COUNT, it returns the number of distinct rows in a
specified table.
Syntax

SELECT COUNT(DISTINCT column_name) FROM table_name;

e.g. To count total number of rows in Department column from PAYMENTS table.
SELECT COUNT(Department) “Deptid” FROM PAYMENTS;

e.g. To count distinct values of column Department from PAYMENTS table.
SELECT COUNT(DISTINCT Department) “Deptid” FROM PAYMENTS;

MAX( )

This function returns the largest value from the selected column.
Syntax

SELECT MAX(column_name) FROM table_name;
e.g. To find the maximum salary of employee from PAYMENTS table.
SELECT MAX(Salary) “Maximum” FROM PAYMENTS;

MIN( ) 

This function returns the smallest value from the selected  column.
Syntax

SELECT MIN(column_name) FROM table_name;
e.g. To display the minimum salary of employee from PAYMENTS table.
SELECT MIN(Salary) “Minimum” FROM PAYMENTS;

SUM( )

This function returns the sum of values in the specified column. The SUM works on numeric fields only. Null values are excluded from the result returned.
Syntax

SELECT SUM(column_name) FROM table_name;
e.g. To count sum of employee’s salary from PAYMENTS table.
SELECT SUM(Salary) “Salary” FROM PAYMENTS;

GROUP BY Statement

The GROUP BY statement is used with the aggregate functions to group the result set by one or more columns.
Syntax

SELECT column_name,
aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name;

HAVING Clause

The HAVING clause is used with GROUP BY clause to place condition because the WHERE keyword could not be used with aggregate functions.
Syntax
SELECT column_name,
aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name
HAVING aggregate_function(column_name)

Example:

If we want to find any of the customers have a total order of less than 2000 from table ORDERS. We use the following SQL statement
SELECT Customer,SUM(OrderPrice)
FROM ORDERS
GROUP BY Customer
HAVING SUM(OrderPrice)<2000;

Example:

SELECT Customer,SUM(OrderPrice)
FROM ORDERS
WHERE Customer=‘Hansen’ OR Customer=‘Jensen’
GROUP BY Customer
HAVING SUM(OrderPrice)>1500;