CODEWITHSUNDEEP
mysql
RDowns
RDowns

Reputation: 659

How to limit decimal places to 2 in this sql query?

I have the following SQL Query:

select u.user_name, sum(r.goals) total_goals, sum(r.goals)/(2 * count(1)) avg_goals 
from (select community_id, player1_id id, player1_goals goals 
from results union all select community_id, player2_id, player2_goals from results) r 
inner join users u on r.id = u.id 
where r.community_id = 16 group by r.id, u.user_name 
ORDER BY avg_goals DESC

Which produces a result such as "avg_goals" , "1.2500" ( I am only interested in this column for this instance)

How do I limit the decimal places in the result to just 2? So the output would be 1.25 instead of 1.2500

Upvotes: 0

Views: 458

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39517

you can use ROUND(col, 2)

select u.user_name, sum(r.goals) total_goals, round(sum(r.goals)/(2 * count(1)),2) avg_goals 
from (select community_id, player1_id id, player1_goals goals 
from results union all select community_id, player2_id, player2_goals from results) r 
inner join users u on r.id = u.id 
where r.community_id = 16 group by r.id, u.user_name 
ORDER BY avg_goals DESC

As @scaisEdge said, You can alternatively use truncate(col,2) if you just want to truncate the rest of the digits.

Upvotes: 3

ScaisEdge
ScaisEdge

Reputation: 133390

You should use TRUNCATE eg:

   SELECT TRUNCATE(1.2500,2) from dual ;


   SELECT TRUNCATE(round(sum(r.goals)/(2 * count(1)),2), 2) .....

Upvotes: 0

Related Questions