Introduction
Function is a type of command which accepts 'n' number of values as input and return a single value. SQL Functions can be of different types:
- sum()
- average()
- maximum()
- minimum()
- count()
Description
The function sum() is used to calculate the set of values or an expression. For example, the sum function is used to add all the column details like salaries of employees.
Syntax
Select Sum<column_name> from <table_name>;
table_name => Any accurate table.
column_name => The operation that one can perform on a column in the table. Examples
Below is an example to understand the concept of sum function in the table.
[sql]sql>select * from employee21;
+--------+-------+-------+--------+
| emp_id | ename | sal | deptno |
+--------+-------+-------+--------+
| 1001 | mike | 12000 | 10 |
| 1002 | rambo | 13000 | 20 |
| 1003 | kate | 14000 | 10 |
| 1004 | jeo | 14000 | 20 |
| 1005 | finn | 14000 | 30 |
+--------+-------+-------+--------+
5 rows in set (0.00 sec)
sql> select sum(sal) from employee21;
+----------+
| sum(sal) |
+----------+
| 67000 |
+----------+
1 row in set (0.00 sec)[/sql]
The above example tells that, when sum() functional operation is performed on a column name like salary, then it will add all the employee salaries and gives the output.
Description
Average function is used to calculate the average of a given arrangement of values or an expression. For example the average function is utilized to compute the average of a column name salary in the table.
Syntax
Select avg(column_name) from <table_name>;
table_name => Any accurate table.
column_name => The operation that one can perform on a column in the table. Examples
Below is an example to understand the concept of average function in the table.
[sql]sql> select * from employee21;
+--------+-------+-------+--------+
| emp_id | ename | sal | deptno |
+--------+-------+-------+--------+
| 1001 | mike | 12000 | 10 |
| 1002 | rambo | 13000 | 20 |
| 1003 | kate | 14000 | 10 |
| 1004 | jeo | 14000 | 20 |
| 1005 | finn | 14000 | 30 |
+--------+-------+-------+--------+
5 rows in set (0.00 sec)
sql>select avg(sal) from employee21;
+------------+
| avg(sal) |
+------------+
| 13400.0000 |
+------------+
1 row in set (0.00 sec)[/sql]
The above example tells that, when avg() functional operation is performed on a column name like salary, then it will average all the employee salaries and gives the output.
Description
Maximum function is utilized to ascertain the most extreme value of a given arrangement of values or an expression. For illustration the maximum function is utilized to locate the maximum of a column name salary in the table.
Syntax
Select maximum<column_name>from <table_name>;
table_name => Any accurate table.
column_name => The operation that one can perform on a column in the table.
Examples
Below is an example to understand the concept of maximum function in the table.
[sql]sql> select * from employee21;
+--------+-------+-------+--------+
| emp_id | ename | sal | deptno |
+--------+-------+-------+--------+
| 1001 | mike | 12000 | 10 |
| 1002 | rambo | 13000 | 20 |
| 1003 | kate | 13500 | 10 |
| 1004 | jeo | 14000 | 20 |
| 1005 | finn | 14500 | 30 |
+--------+-------+-------+--------+
5 rows in set (0.00 sec)
sql> select max(sal) from employee21;
+----------+
| max(sal) |
+----------+
| 14500 |
+----------+
1 row in set (0.00 sec)[/sql]
The above example tells that, when max() functional operation is performed on a column name like salary, then it will display the highest salary of employee.
Description
Minimum function is utilized to compute the base estimation of a given arrangement of values or an expression. For illustration the minimum function is utilized to locate the minimum of a column name salary in the table.
Syntax
Select minimum<column_name>from <table_name>;
table_name => Any accurate table.
column_name => The operation that one can perform on a column in the table.
Examples
Below is an example to understand the concept of minimum function in the table.
[sql]sql> select * from employee21;
+--------+-------+-------+--------+
| emp_id | ename | sal | deptno |
+--------+-------+-------+--------+
| 1001 | mike | 12000 | 10 |
| 1002 | rambo | 13000 | 20 |
| 1003 | kate | 14000 | 10 |
| 1004 | jeo | 14500 | 20 |
| 1005 | finn | 14300 | 30 |
+--------+-------+-------+--------+
5 rows in set (0.00 sec)
sql> select min(sal) from employee21;
+----------+
| min(sal) |
+----------+
| 12000 |
+----------+
1 row in set (0.00 sec)[/sql]
The above example tells that, when min() functional operation is performed on a column name like salary, then it will display the lowest salary of employee.
Description
Count function is utilized to compute the aggregate estimation of a given set of values or an expression. For illustration the count function is utilized to locate the aggregate number of employees in the table.
Syntax
select count<column_name> from <table_name>;
table_name => Any accurate table.
column_name => The operation that one can perform on a column in the table.
Examples
Below is the example to understand the concept of count function in the table.
[sql]sql> select * from employee21;
+--------+-------+-------+--------+
| emp_id | ename | sal | deptno |
+--------+-------+-------+--------+
| 1001 | mike | 12000 | 10 |
| 1002 | rambo | 13000 | 20 |
| 1003 | kate | 14000 | 10 |
| 1004 | jeo | 14000 | 20 |
| 1005 | finn | 14000 | 30 |
+--------+-------+-------+--------+
5 rows in set (0.00 sec)
sql> select count(emp_id) from employee21;
+---------------+
| count(emp_id) |
+---------------+
| 5 |
+---------------+
1 row in set (0.00 sec)[/sql]
The above example tells that, when count() functional operation is performed on a column name like emp_id, then it will count all the employees in the table and display the output as 5.
Key Points
- Group function - Is used to accept 'n' number of values and return a single value.
- Sum functions - Adds, average calculates the average of set of values.
- Maximum and minimum functions - Calculates the maximum and minimum values in given set of values.
- Count - Gives total values.