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
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, SUM(social + math + science ) as total,
truncate(SUM(social + math + science )/3,2) as avg from student3 group by id