Page 1 of 1

issue in decimal places

Posted: Fri May 19, 2006 1:35 am
by itsmani1

Code: Select all

mysql_query("SELECT avg( vote ) as average, sum( vote ) as total, count( vote ) as total_v FROM story_vote where sid = $poetryid") or die(mysql_error());
in result it shows me 3.00000 and i want it to show 3.0

Posted: Fri May 19, 2006 2:11 am
by andym01480
Use the ROUND function in the query

The following found using google

Code: Select all

ROUND(x) and ROUND(x,y)
Returns the value of x rounded to the nearest integer. ROUND can also accept an additional argument y that will round x to y decimal places.

select ROUND(14.492);
+---------------+
| ROUND(14.492) |
+---------------+
|            14 |
+---------------+
1 row in set (0.00 sec)

select ROUND(4.5002);
+---------------+
| ROUND(4.5002) |
+---------------+
|             5 |
+---------------+
1 row in set (0.00 sec)

select ROUND(-12.773);
+----------------+
| ROUND(-12.773) |
+----------------+
|            -13 |
+----------------+
1 row in set (0.00 sec)

select ROUND(7.235651, 3);
+--------------------+
| ROUND(7.235651, 3) |
+--------------------+
|              7.236 |
+--------------------+
1 row in set (0.00 sec)

Posted: Fri May 19, 2006 2:29 am
by Christopher
You can use the MySQL TRUNCATE(value, ndigits) function in the query or use the PHP number_format() function on the returned values.

Posted: Fri May 19, 2006 2:32 am
by andym01480
Mmmm learnt something there. Is that better than Round or have I got it wrong again!

Posted: Fri May 19, 2006 3:42 am
by onion2k
Actually you want to use ROUND() .. but make sure you use 2 arguments .. eg ROUND(columnname, 1). If your number is 1.678, and you use TRUNCATE(1.678,1) you'll get 1.6 .. if you use ROUND(1.678,1) you'll get 1.7. TRUNCATE() just chops off the rest of the number, it's very unlikely that's what you actually want.

If you're outputting the number to the screen rather than using it mathematically, consider using FORMAT() instead.