1

This is my code

SELECT  AVG(`page_rate` )
FROM `pages` 
   INNER JOIN `languages` ON `pages`.`lang_id`=`languages`.`id` 
WHERE `abbr` IN ("EN-UK", "EN-US", "EN-AU")

How can I display only 2 digits after decimal point?

I tried this:

SELECT AVG( cast(`page_rate` as decimal(10,2))) 
FROM `pages` 
    INNER JOIN `languages` ON `pages`.`lang_id`=`languages`.`id` 
WHERE `abbr` IN ("EN-UK", "EN-US", "EN-AU")

but result is 20.166667 - not only 2 digits

  • 3
    Added mysql tag based on the usage of those backticks – a_horse_with_no_name Mar 30 '16 at 18:24
4

Switch your AVG and CAST. Instead of

SELECT AVG( cast(`page_rate` as decimal(10,2)))

Use

SELECT CAST(AVG(`page_rate`) as decimal(10,2))

Then you are casting your average with 2 decimal places.

0

You might find helpful the question at: https://stackoverflow.com/questions/11190668/format-number-to-2-decimal-places

Note that the linked post has an answer by jmarceli that handles rounding or not rounding, according to your need.

A snippet of code from his post:

Without rounding:

TRUNCATE(0.166, 2)
-- will be evaluated to 0.16

With rounding:

ROUND(0.166, 2)
-- will be evaluated to 0.17
0

That's a formatting problem. So use FORMAT():

SELECT FORMAT(123.4567, 2); --> 123.46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.