# Restricting Numbers to fixed decimal places using truncate() function

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 upto which the number is to be truncated. We will try some examples.
``````
SELECT truncate( 3.56, 1 ); // Ouput is 3.5
select truncate(8.7689,2); // output is 8.76
select truncate(342.87,0); // output is 342
``````

## 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.
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 ## Subscribe

* indicates required
Subscribe to plus2net plus2net.com

Post your comments , suggestion , error , requirements etc here .