MYSQL FUNCTIONS

Aggregate Functions:  Some time it is required to apply a Select query in a group of records instead of whole table.  You can group records by using GROUP BY clause with Select command. A group column is chosen which have non-distinct (repeating) values like City, Job etc.  Generally, the following Aggregate Functions [MIN(), MAX(), SUM(), AVG(), COUNT()] etc. are applied on groups. SUM() Returns the sum of given column. MIN() Returns the minimum value in the given column. MAX() Returns the maximum value in the given column. AVG() Returns the Average value of the given column. COUNT() Returns the total number of values/ records as per given column. Example: mysql> Select Sum(Sal) from EMPLOYEE; mysql> Select Min(Sal) from EMPLOYEE; mysql> Select Max(Sal) from EMPLOYEE;  Aggregate Functions & NULL Values: Consider the following table EMP with some records. mysql> Select Sum(Sal) from EMP;  12000 mysql> Select Min(Sal) from EMP;  3500 mysql> Select Max(Sal) from EMP;  4500 mysql> Select Count(Sal) from EMP;  3 mysql> Select Avg(Sal) from EMP;  4000 mysql> Select Count(*) from EMP;  5 mysql> Select Count(Code) from EMP;  5 :: 76 ::  Aggregate Functions & Group (Group By Clause): An Aggregate function may applied on a column with DISTINCT or ALL keyword. If nothing is given, ALL scope is assumed.  Using SUM () This function returns the sum of values in given column or expression. mysql> Select Sum(Sal) from EMP; mysql> Select Sum(DISTINCT Sal) from EMP; mysql> Select Sum (Sal) from EMP where City=‘Kanpur’; mysql> Select Sum (Sal) from EMP Group By City; mysql> Select Job, Sum(Sal) from EMP Group By Job;  Using MIN () This function returns the Minimum value in the given column. mysql> Select Min(Sal) from EMP; mysql> Select Min(Sal) from EMP Group By City; mysql> Select Job, Min(Sal) from EMP Group By Job;  Using MAX () This function returns the Maximum value in given column. mysql> Select Max(Sal) from EMP; mysql> Select Max(Sal) from EMP where City=‘Kanpur’; mysql> Select Max(Sal) from EMP Group By City;  Using AVG () This function returns the Average value in the given column. mysql> Select AVG(Sal) from EMP; mysql> Select AVG(Sal) from EMP Group By City;  Using COUNT (<*|column>) This functions returns the number of rows in the given column. mysql> Select Count (*) from EMP; mysql> Select Count(Sal) from EMP Group By City; mysql> Select Count(*), Sum(Sal) from EMP Group By Job;  Aggregate Functions & Conditions on Groups (Having Clause): You may use any condition on group, if required. HAVING clause is used to apply a condition on a group. mysql> Select Job, Sum(Pay) from EMP Group By Job HAVING Sum(Pay)>=8000; mysql> Select Job, Sum(Pay) from EMP Group By Job HAVING Avg(Pay)>=7000; mysql> Select Job, Sum(Pay) from EMP Group By Job HAVING Count(*)>=5; mysql> Select Job, Min(Pay),Max(Pay), Avg(Pay) from EMP Group By Job HAVING Sum(Pay)>=8000; mysql> Select Job, Sum(Pay) from EMP Where City=‘Dehradun’ Group By Job HAVING Count(*)>=5; WHERE V/s HAVING: Where clause works in respect of whole table but Having works on Group only. If Where and Having both are used then Where will be executed first. WHERE is used to put a condition on individual row of a table whereas HAVING is used to put condition on individual group formed by GROUP BY clause in a SELECT statement.