Mathematical functions in MySQL
SQL stands for ‘Structured Query Language’. As the name already suggests, the data is stored in the form of rows and columns, in a structured format. Using the SQL language, we can store, retrieve, and manipulate the data stored in the MySQL database.
Sometimes, the database table contains thousands of entries. For example, a table containing the salary information of thousands of its employees. Now suppose, the company’s manager wants to calculate the average salary of the employees, or wants to calculate the total amount of money he is spending on paying the salary to his employees. It will be very hectic for him to manually do the job as it will be very much time and energy consuming. Thus, there are some mathematical functions in MySQL that can be used in such cases.
Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course by GeeksforGeeks.
For the sake of convenience, we will consider the table_name as ’employees’ and the ‘column_name’ containing information about the salaries of the employees as ‘salary’ for this whole article.
In this article, we will be looking at 5 such functions:
1. COUNT() 2. AVG() 3. SUM() 4. MIN() 5. MAX()
Let’s have a look at each of them, one by one.
- COUNT() Function:
The COUNT() function returns the number of rows that match specified criteria.
For example, the manager wants to find the number of employees whose salary is greater than or equals to Rs.40, 000 per month. Using COUNT() function, he can create a general MySQL query to fetch the required data.
SELECT COUNT(salary) FROM employees WHERE salary >= 40000;
This will fetch the information of all of his employees whose salary either equals to 40, 000 or is greater than that.
- AVG() Function:
The AVG() function will help the manager in determining the average salary of all of his employees.
The output will be returned in the form of int.
SELECT AVG(salary) FROM employees;
- SUM() Function:
The SUM() function returns the total sum of a numeric column.
This will help the manager in finding the total amount of money he is spending in giving away the salaries of his employees.
SELECT SUM(Price) FROM Products;
- MAX() Function:
As the name already suggests, the MAX() function returns the largest value of the selected column. This means that the manager can use this function to find the employee that is drawing the maximum salary in his company.
The working code of the function will be;
SELECT MAX(column_name) FROM table_name;
- MIN() Function:
The MIN() function returns the smallest value of the selected column. This function can be to find the information about the employee that is drawing the minimum salary from the company.
The working code of this function will be;
SELECT MIN(column_name) FROM table_name;