truncate(): Restricting Numbers to fixed decimal places

SELECT truncate( 3.56, 1 ); // Output is 3.5
select truncate(8.7689,2); // output is 8.76
select truncate(342.87,0); // output is 342
We can truncate any number to fixed decimal value by using truncate function in mysql. Here is the syntax
truncate(N,D);
Here n is the number and D is the places up to which the number is to be truncated. 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, SUM(social + math + science ) as total, 
	SUM(social + math + science )/3 as avg from student3 group by id
idnamesocialmathsciencetotalavg
2Max Ruin85855622675.3333
3Arnold55754017056.6667
4Krish Star60705018060.0000
5John Mike60908023076.6667
6Alex John55809022575.0000
7My John Rob78706020869.3333
8Asruid85908025585.0000
9Tes Qry78706020869.3333
10Big John55554015050.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, SUM(social + math + science ) as total, 
  truncate(SUM(social + math + science )/3,2) as avg from student3 group by id
The output of this query is here.
idnamesocialmathsciencetotalavg
2Max Ruin85855622675.33
3Arnold55754017056.66
4Krish Star60705018060.00
5John Mike60908023076.66
6Alex John55809022575.00
7My John Rob78706020869.33
8Asruid85908025585.00
9Tes Qry78706020869.33
10Big John55554015050.00
Download sql dump of student3 table
SQL Math References Standard deviation Average value Query
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer