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.
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
2 Max Ruin 85 85 56 22675.333
3 Arnold5575 40 17056.6667
4 Krish Star60 70 50 18060.0000
5 John Mike60908023076.6667
6 Alex John55809022575.0000
7 My John Rob78706020869.3333
8Asruid85 908025585.000
9Tes Qry78706020869.3333
10Big John 55 554015050.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
2 Max Ruin 85 85 56 22675.33
3 Arnold5575 40 17056.66
4 Krish Star60 70 50 18060.00
5 John Mike60908023076.66
6 Alex John55809022575.00
7 My John Rob78706020869.33
8Asruid85 908025585.00
9Tes Qry78706020869.33
10Big John 55 554015050.00
Download sql dump of student3 table

Be the first to post comment on this article :


Google+

Post Comment This is for short comments only. Use the forum for more discussions.






HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us
©2000-2015 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer