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

Subscribe to our mailing list

* indicates required
Subscribe to plus2net
Visitors Rating
Your Rating


Google+

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




Subscribe to our mailing list

* indicates required
Subscribe to plus2net




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