Some Useful Math Functions in MySQL

Assalam-o-Alaikum! Dear visitors today I will tell you about some maths functions available in MySQL. In past, I often used PHP functions to calculate desired values but later I realized that MySQL has powerful functions that calculate data based on given criteria. We are not going to discuss all functions here but some basic and useful functions will be discussed today.

As you know PHP and MySQL love each other like Romeo and Juliet so often you need to calculate some values to get your desired value. Now you have two options, first get values from MySQL through query and then perform calculations via PHP functions, 2nd to perform a calculation in MySQL directly and get required value without further process. The 2nd approach is faster than 1st but most of us don’t know that how to perform a calculation in MySQL. So let’s start our first trick.

The first function that we will discuss is SUM() functions. As its name suggests, it sums values. For example, we have a table named “numbers” and it has following columns and data :

Note: We will work on this table throughout this tutorial.

id	student_Id	marks
1	10	100
2	12	150
3	13	50
4	14	60
5	15	10
6	16	15
7	17	56
8	18	89
9	19	190
10	20	200

Ok now you want to sum values where student_id is greater than 5, the query will be like this:-

where numbers.marks is “marks” column in numbers table and numbers.student_id is “student_id” column from numbers table. You can write this query as follows:

There are MIN() and MAX() functions that will fetch the minimum and maximum value from a column. To get Max value from the above-mentioned table simply write this query:

and for getting minimum value write this query:

You can change WHERE clause from > 5 to any other value. For example, if you want to get maximum value from the record that is less than or equal to 4, you will write this query:-

You are not limited to perform calculations just on table columns but you can also use some external numbers to perform calculations. For example, you want to add 30 in in maximum value returned from above example, you will write this query:

and you want to get a random number higher then the maximum number you will write:

There are too many functions in MySQL but I only discussed that I used in my projects. You can search on the web for more functions. For reference, I am giving a link to official MySQL website’s page where you can find more math functions.

https://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html

Hope you enjoyed this tutorial.