truncate mysql function to truncate decimal places of any number

SELECT TRUNCATE(3.56, 1);    -- 3.5
SELECT TRUNCATE(8.7689, 2);  -- 8.76
SELECT TRUNCATE(342.87, 0);  -- 342

We can truncate a number to a fixed number of decimal places by using truncate function in mysql. Here is the syntax

TRUNCATE(N, D)

Here N is the number and D is the number of decimal places. TRUNCATE() removes extra digits without rounding; D can also be negative to truncate digits to the left of the decimal point. We will try some examples.

Truncate Number to 2 decimal places

We have a student table where each student secured marks in three subjects. Let us calculate the average mark obtained by each student.
You can download the sql dump of the student table at end of this page.
Here is the query

SELECT id, name, social, math, science,
       social + math + science AS total,
       (social + math + science) / 3 AS AVG
FROM student3
ORDER BY id;

id	name	social	math	science	total	avg
2	Max Ruin	85	85	56	226	75.3333
3	Arnold	55	75	40	170	56.6667
4	Krish Star	60	70	50	180	60.0000
5	John Mike	60	90	80	230	76.6667
6	Alex John	55	80	90	225	75.0000
7	My John Rob	78	70	60	208	69.3333
8	Asruid	85	90	80	255	85.0000
9	Tes Qry	78	70	60	208	69.3333
10	Big John	55	55	40	150	50.0000

Now let us use the truncate command and restrict the decimal to 2 places only.
Here is the new query.

SELECT id, name, social, math, science,
       social + math + science AS total,
       TRUNCATE((social + math + science) / 3, 2) AS AVG
FROM student3
ORDER BY id;

The output of this query is here.

id	name	social	math	science	total	avg
2	Max Ruin	85	85	56	226	75.33
3	Arnold	55	75	40	170	56.66
4	Krish Star	60	70	50	180	60.00
5	John Mike	60	90	80	230	76.66
6	Alex John	55	80	90	225	75.00
7	My John Rob	78	70	60	208	69.33
8	Asruid	85	90	80	255	85.00
9	Tes Qry	78	70	60	208	69.33
10	Big John	55	55	40	150	50.00

Download sql dump of student3 table
SQL Math References Standard deviation Average value Query

All Math Functions ROUND()

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

truncate(): Restricting Numbers to fixed decimal places

Truncate Number to 2 decimal places

Subhendu Mohapatra