CODEWITHSUNDEEP
mysql
PiDO
PiDO

Reputation: 291

How can I make the decimal places of AVG function in sql limit to 2 only?

I want to limit the decimal of the average to 2..

SELECT grade.GStudNo, AVG(grade.Grade) AS Average, students.LName, students.FName, students.MName, students.Course
FROM students INNER JOIN grade ON students.StudNo = grade.GStudNo
WHERE GSem = '$sem' AND GYear = '$year'
GROUP BY grade.GStudNo
ORDER BY Average ASC LIMIT 3

Upvotes: 24

Views: 57697

Answers (3)

Álvaro Agüero
Álvaro Agüero

Reputation: 4810

Maybe you are looking for this :

SELECT grade.GStudNo, CAST(AVG(grade.Grade) AS DECIMAL(10,2)) AS Average, students.LName, students.FName, students.MName, students.Course
FROM students INNER JOIN grade ON students.StudNo = grade.GStudNo
WHERE GSem = '$sem' AND GYear = '$year'
GROUP BY grade.GStudNo
ORDER BY Average ASC LIMIT 3

this get only 2 decimals

CAST(AVG(grade.Grade) AS DECIMAL(10,2))

Upvotes: 9

DhruvPathak
DhruvPathak

Reputation: 43265

SELECT grade.GStudNo, ROUND( AVG(grade.Grade),2 ) AS Average, students.LName, students.FName, students.MName, students.Course
FROM students INNER JOIN grade ON students.StudNo = grade.GStudNo
WHERE GSem = '$sem' AND GYear = '$year'
GROUP BY grade.GStudNo
ORDER BY Average ASC LIMIT 3

Would round it to two places.

Upvotes: 57

Randy
Randy

Reputation: 16673

use the ROUND function to wrap the AVG calculation...

Upvotes: 16

Related Questions