MySQL - SPLessons

MySQL Functions

Chapter 6

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

MySQL Functions

MySQL Functions

shape Introduction

Functions Function is a type of command which accepts ‘n’ number of values as input and return a single value. MySQL Functions can be of different types:

shape Conceptual
figure

Sum() – Calculate the set of values of an expression
Avg() – Perform the average of an expression
Maximum() – Perform the maximum value of an expression
Minimum() – Perform the minimum value of an expression
Count() – Perform count of an expression

Sum

shape 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.

shape 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.

shape Examples

Below is an example to understand the concept of sum function in the table.

mysql>select * from employee;
+--------+-------+-------+--------+
| emp_id | ename | salary| 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)

mysql> select sum(salary) from employee;
+-----------+
|sum(salary)|
+-----------+
|    67000  |
+-----------+
1 row in set (0.00 sec)

mysql> select sum(salary)as total salary from employee;
+--------------+
| Total salary |
+--------------+
|    67000     |
+--------------+
1 row in set (0.00 sec)

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.

Average

shape 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.

shape 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. 

shape Examples

Below is an example to understand the concept of average function in the table.

mysql> select * from employee;
+--------+-------+-------+--------+
| emp_id | ename | salary| 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)

mysql>select avg(salary) from employee;
+------------+
| avg(salary)|
+------------+
| 13400.0000 |
+------------+
1 row in set (0.00 sec)

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.

Maximum()

shape 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.

shape 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.

shape Examples

Below is an example to understand the concept of maximum function in the table.

mysql> select * from employee;
+--------+-------+-------+--------+
| emp_id | ename | salary| 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)

mysql> select max(salary) from employee;
+-----------+
|max(salary)|
+-----------+
|    14500  |
+-----------+
1 row in set (0.00 sec)

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.

Minimum()

shape 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.

shape 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.

shape Examples

Below is an example to understand the concept of minimum function in the table.

mysql> select * from employee;
+--------+-------+-------+--------+
| emp_id | ename | salary| 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)

mysql> select min(salary) from employee;
+------------+
| min(salary)|
+------------+
|    12000   |
+------------+
1 row in set (0.00 sec)

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.

Count()

shape 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.

shape 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.

shape Examples

Below is the example to understand the concept of count function in the table.

mysql> select * from employee;
+--------+-------+-------+--------+
| emp_id | ename | salary| 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)

mysql> select count(emp_id) from employee;
+---------------+
| count(emp_id) |
+---------------+
|             5 |
+---------------+
1 row in set (0.00 sec)

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.

Summary

shape 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.