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