mysql math functions

Hi all, how is it going on? Hoping you are fine. Today we will learn some useful MySQL math functions that will help you a lot in your future projects. As you all know that MySQL is a powerful database system that stores and fetches records in a speedy way. You just have to write some lines of queries and all done.

In the past, some times, I used PHP functions to calculate values but later I realized that MySQL has some useful math functions that can calculate data based on given criteria. So, we are not going to discuss all those functions that are present in MySQL but, some most useful functions will be discussed today.

Table of Contents

Introduction to MySQL Math Functions

As you know PHP and MySQL love each other Laila and Majnoo. We, therefore, some times use PHP to get the desired result that comes from MySQL. But I prefer to calculate the result in MySQL as possible then perform further action in PHP.

I think is easy to perform a calculation in MySQL directly, and get the required value and pass it to PHP for further processing. This approach is faster but most of us don’t know how to perform a calculation in MySQL. If you already know functions mentioned in Table of Contents then leave this page and go to another page otherwise keep reading.

Where to use?

MySQL math functions can be used in different ways. For example, you want to select Maximum value from a list of records. You want to SUM the numeric values with the given criteria. If you need random value or you need square root etc., in all these cases you have to use MySQL math functions rather then using PHP functions.

But remember not all results can be grabbed with MySQL math functions you sometimes, need to use PHP functions. It depends on the situation whether you will use MySQL or PHP. Now let’s start with a list of records that we will use in most functions.

Example Records

idstudent_Idmarks
110100
212150
31350
41460
51510
61615
71756
81889
919190
1020200
“students” Table

Carefully look at the above table. We will use this table in MIN(), MAX(), SUM(), COUNT(), and RAND() functions.

MySQL COUNT() and SUM() Functions

The first two functions that we will discuss are COUNT () and SUM() functions. As its names suggest, these functions are used to COUNT and SUM the values respectively. For example, from the above table, if you want to COUNT the number of records with criteria then you will use COUNT() function like below:

SELECT count(marks) as 'Total Records' from students where id >1

The above query will return 9 as there are 9 records where “id” is greater than 1. The query is very simple, we just select a column “marks” with COUNT() function and name it “Total Records”. We select all these from the “students” table where “id” is greater than 1. You can write this query as under:

SELECT count(id) as 'Total Records' from students where student_id >13

This query will return “7” as can be seen in the query. I think no need to explain because the query is very simple to understand. If you don’t want to use “Total Records” then just write a more simple query.

SELECT count(id) from students where student_id >13

Now let’s move to SUM() function. Just like COUNT() function SUM() function will Sum the numbers of given criteria. We will use the same table. Look at the example query below:

SELECT SUM(marks) from students where student_id >13

In the above query, we select the “marks” column with SUM() function from the “students” table where student_id is greater than 13 so we will get 620.

MySQL MIN() and MAX() Functions

Now we will use min and max in MySQL. MIN() and MAX() is very simple functions. MIN() will select a minimum value from range and MAX() will return the maximum or largest number from the range. Both functions are used in the below queries:

-- Example 1 -- Using MIN() without any criteria
SELECT MIN(marks) from students
-- Example 2 -- Using MIN() with criteria
SELECT MIN(marks) from students WHERE marks > 100

-- Example 3 -- Using MAX() without any criteria
SELECT MAX(marks) from students
-- Example 4 -- Using MAX() with criteria
SELECT MAX(marks) from students WHERE marks < 100

Above 4 queries will return 10, 150, 200, and 89 respectively.

Taking Square Root and Generate Random Number in MySQL

Taking square root in mysql is also very easy. You can use this function anywhere in your query to produce square root of any number

SELECT SQRT(16)

Did you see? it is so easy to take square root in MySQL. Just right function name and pass it a number. In the above example, it will return the square root of 16 is 4. Now let’s move to our next function which is RAND(). Just like SQRT, generating a random number in MySQL is also very easy. Look below:

SELECT RAND()

Above statement will return any random number between 0 to 1. However, if you need random number on specific number then use below statement:

SELECT RAND()*20

It will generate a random number from 0 to 20. But these queries will generate a decimal number. If you need integer then you have to use FLOOR() along with RAND().

SELECT FLOOR(RAND()*20)

CEIL() and FLOOR() in MySQL

The CEIL() function takes an input number and returns the smallest integer greater than or equal to that number.

The following shows the syntax of the CEIL() function:

CEIL (numeric_expression)

In the above syntax, the “numeric_expression” can be a literal number or an expression that evaluates to a number. You can see below example for better understanding:

SELECT CEIL(4.62);

The above statement will return 5 because the smallest integer greater than or equal the input number is 5.

Now we will talk about FLOOR(). The FLOOR function accepts one argument which can be number or numeric expression and returns the largest integer number less than or equal to the argument. Look at below example SQL statement:

SELECT FLOOR(4.34);

The result is 4 because it is the largest integer which is less than or equal to 4.34

Conclusion

There are many other math functions that can be used in MySQL like MOD(), ROUND(), ABS(), POWER() etc., but the function that we used are most useful and you have to keep in mind these function all the time

Bye Bye

So you are going to leave this page? are you sure? OK as you wish but here are some more tutorials that you may like to read. Please take a look at one of them.

Also check out our PHP tutorials HERE.

Leave a Reply - I will show after approval