Open In App

Mathematical functions in MySQL

Last Updated : 07 Aug, 2019
Like Article

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.

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.

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

  2. 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;
  3. 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;
  4. 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;
  5. 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;

Previous Article
Next Article

Similar Reads

SQL Server Mathematical functions (SQRT, PI, SQUARE, ROUND, CEILING & FLOOR)
Mathematical functions are present in SQL server which can be used to perform mathematical calculations. Some commonly used mathematical functions are given below: 1. SQRT(): SQRT() function is the most commonly used function. It takes any numeric values and returns the square root value of that number. Syntax: SELECT SQRT(..value..) Example: 2. PI
1 min read
MySQL | Common MySQL Queries
MySQL server is a open-source relational database management system which is a major support for web based applications. Databases and related tables are the main component of many websites and applications as the data is stored and exchanged over the web. Even all social networking websites mainly Facebook, Twitter, and Google depends on MySQL dat
9 min read
DATABASE() Function The DATABASE() Function in MySQL returns the name of the default or current database. The string or name returned by DATABASE() function uses the utf8 character set. If there is no default database,the Database function returns NULL. In the older versions than MySQL 4.1.1,The Database function used to return an empty string, if
2 min read
MySQL | Ranking Functions
The ranking functions in MySQL are used to rank each row of a partition. The ranking functions are also part of MySQL windows functions list. These functions are always used with OVER() clause.The ranking functions always assign rank on basis of ORDER BY clause.The rank is assigned to rows in a sequential manner.The assignment of rank to rows alway
3 min read
MINUTE(), MICROSECOND() and HOUR() functions in MySQL
1. MINUTE() : The MySQL MINUTE() function is used for return the minute part of a datetime value. It can be between 0 to 59.When the datetime is passed in MINUTE() function then it will return the minute value . Syntax - MINUTE(datetime) Parameter - It take parameter a dateline value. Return - It returns a minute numeric value between 0 to 59. Exam
2 min read
LCASE() or LOWER() Functions in MySQL
1. LCASE() : The LCASE() function used to convert a text to lower-case. This function is a similar to the LOWER() function. Syntax : SELECT LCASE(text) Example : SELECT LCASE("Reading on GEEKSFORGEEKS is FUN") AS LowerText; Output - LowerText reading on geeksforgeeks is fun 2. LOWER() : Syntax : SELECT LOWER(text) Example : SELECT LOWER("Studying o
1 min read
Various String, Numeric, and Date & Time functions in MySQL
A function is a special type of predefined command set that performs some operation and returns a single value. Functions operate on zero, one, two or more values that are provided to them. The values that are provided to functions are called parameters or arguments. The MySQL functions have been categorized into various categories, such as String
3 min read
MySQL | LEAD() and LAG() Function
The LEAD() and LAG() function in MySQL are used to get preceding and succeeding value of any row within its partition. These functions are termed as nonaggregate Window functions. The Window functions are those functions which perform operations for each row of the partition or window. These functions produce the result for each query row unlikely
3 min read
The MySQL UPDATE query is used to update existing records in a table in a MySQL database. It can be used to update one or more field at the same time. It can be used to specify any condition using the WHERE clause. Syntax : The basic syntax of the Update Query is - Implementation of Where Update Query : Let us consider the following table "Data" wi
2 min read
PHP | MySQL Database Introduction
What is MySQL? MySQL is an open-source relational database management system (RDBMS). It is the most popular database system used with PHP. MySQL is developed, distributed, and supported by Oracle Corporation. The data in a MySQL database are stored in tables which consists of columns and rows.MySQL is a database system that runs on a server.MySQL
4 min read
Article Tags :