set decimal places of sum in sql

user_737114 0
Viewed 64738

I have a database I use for a debating competition I am trying to sort the standings out to see which schools will play off in the finals at the end of the semester.

I have this working as shown below however I am not able to round the ptc field, it currently returns up to 4 decimal places eg 0.6667 if they have won 2 out of 3 debates.

    SELECT   t.id,
             t.name                  AS name,
             SUM(t.id  = d.winnerid) AS w,
             SUM(t.id != d.winnerid) AS l,
             SUM(t.id  = d.winnerid)/(SUM(t.id  = d.winnerid)+SUM(t.id != d.winnerid)) AS ptc
    FROM     debates AS d
        JOIN teams   AS t ON t.id IN (d.hostid, d.visitid)
    WHERE    d.visitid != -1
         AND d.debatedate < CURDATE()
    GROUP BY t.id
    ORDER BY ptc DESC

I believe I have to use decimal(2,2) here however I am not able to get the syntax right I have tried a few diferent things like

SUM(t.id  = d.winnerid)/(SUM(t.id  = d.winnerid)+SUM(t.id != d.winnerid)) AS ptc decimal (2,2)

Am happy to provide more information about the tables if required but I don't think it is required?

decimal sum mysql sql
2 Answers

For the ptc field Round, 2 decimal place...

Repalce

This line,

SUM(t.id  = d.winnerid)/(SUM(t.id  = d.winnerid)+SUM(t.id != d.winnerid)) AS ptc

To,

This Line!

ROUND(SUM(t.id  = d.winnerid)/(SUM(t.id  = d.winnerid)+SUM(t.id != d.winnerid)),2) AS ptc
user_135168730
Related