MySQL 在sql中设置总和的小数位
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20812835/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
set decimal places of sum in sql
提问by ak85
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.
我的工作如下所示，但是我无法舍入 ptc 字段，如果他们赢得了 3 场辩论中的 2 场，它目前最多返回 4 个小数位，例如 0.6667。
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
我相信我必须在这里使用小数（2,2）但是我无法正确使用语法我尝试了一些不同的东西，例如
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?
如果需要，很乐意提供有关表格的更多信息，但我认为不需要？
回答by Java Curious ?
try this.
尝试这个。
SELECT ROUND(SUM(cash), 2)
FROM <tablename>
If you are getting no results, then there must be a null value, try this instead.
如果你没有得到任何结果，那么一定有一个空值，试试这个。
SELECT ROUND(SUM(cash), 2)
FROM<tablename> a
WHERE cash IS NOT NULL
Here is a simple demo of it :
这是它的一个简单演示：
Update :
更新 ：
SELECT round( ROUND(SUM(p.prod_price = l.prod_unit_price), 2)
/ROUND(SUM(p.prod_id = l.prod_id), 2),2)
FROM b2b.product_master p
join b2b.move_cart_item_master l;
