SQL Functions

Like as any other programming language, SQL also supports a rich set of in-build functions. These functions greatly increase the ability to manipulate the information stored in a database. These functions are also referred as Oracle Functions.

SQL Functions

SQL functions accept arguments as an input and returns result as an output Arguments can be some constant values, or expressions, or column names related to tables. The general format of the SQL function is as given below:

Function_name(argument1, argument2,…,argument)

SQL functions can be categorized into two parts, based on whether they operate on a single row or a set or rows.

Aggregate functions (Group function)

-         Functions that operate on a set or rows are called Aggregate or Group function.

-         These functions accept a set or rows, i.e. a group, as an input. They return only a single row as an output. So, if five rows are given as an input, there will be only single result as an output.

-         For example, MAX is an aggregate function. It finds maximum number out of set of numbers, and returns single value as an answer.

6 List Types of Aggregate functions

1.     MAX

2.     MIN

3.     SUM

4.     AVG

5.     COUNT(*)

6.     COUNT

EID	ENAME	BIRTHDATE	SALARY	CITY
E01	Tulsi	26-JAN-2001	13000	Anand
E02	Gopi	10-Feb-2004	16000	Vvnagar
E03	Rajshree	31-Jul-2006	17000	Anand
E04	Vaishali	12-MAR-2008	13000	Mehsana
E05	Laxmi	12-MAR-2008	25000
E06	Shivali	01-SEP-2010	20000	Surat

1.     MAX

MAX (ColumnName)

Returns maximum value for a given column.

EX:-  Find maximum salary from Employee table.

         SELECT MAX (salary) “Max Salary” FROM Employee;

Output:-  Max Salary

                  25000

database administrators

2.     MIN

MIN (ColumnName)

Returns minimum value for a given column.

EX:- Find minimum salary from Employee table.

        SELECT MIN (Salary) “Min Salary” FROM Employee;

Output:- Min Salary

                13000

3.     SUM

SUM ([ Distinct | All ] columnName)

Returns sum of all vales for a given column

If ‘distinct’ is provided, duplicate values will be considered only once. By default, ‘All’ is considered. This is illustrated in following example.

EX:- Find sum of all and distinct salaries from Employee table.

            SELECT SUM (salary), SUM (Distinct salary) FROM Employee;

Output:- SUM (SALARY)               SUM (DISTINCTSALARY)

                  104000                           79000           

4.     AVG

AVG ([ Distinct |All ] columnName)

Returns average of all values for a given column.

If ‘distinct’ is provided, duplicate values will be considered only once. By default, ‘All’ is considered. This is illustrated in following table.

EX:- Find average of all salaries from Employee table.

            SELECT AVG (Salary), AVG (Distinct salary) FROM Employee;

Output:- AVG(SALARY)                 AVG(DISTiNCTSALARY)

                 17.333.333                     15800

5.     COUNT(*)

COUNT ( * )

Returns number of rows in a table including duplicates and having null values.

EX:- Find total number of employees.

            SELECT COUNT (*) FROM Employee;

Output:- COUNT(*)

                    6

6.     COUNT

COUNT ([ Distinct | All ] columnName )

Returns number of rows where column does not contain null value.

EX:- The following example clarifies differences between count and count (*)

            SELECT COUNT (*) “C*”, COUNT(city) “CC”,

            COUNT (Distinct city) “CDC” FROM Employee;

Output:- C*              CC                   CDC

                 6                 5                      4