I am trying to divide sum of two columns and SQL is not giving me precise number in decimal.Please check below queries. I don't know what I am missing here.
我试图划分两列的总和,SQL不给我十进制的精确数字。请检查下面的查询。我不知道我在这里缺少什么。
Data type of PAYEE and TOTAL is decimal(10,2)
PAYEE和TOTAL的数据类型是十进制(10,2)
Queries
SELECT SUM(PAYEES) AS SUM_PAYEES,SUM(TOTAL) AS SUM_TOTAL,SUM(PAYEES)/SUM(TOTAL) AS DIVISION FROM #ADMIN_FEE_1
SUM_PAYEES SUM_TOTAL DIVISION
19940.00 59435.00 0.335492
SELECT 19940.00/59435.00 AS DIVISION
DIVISION
0.3354925548
Is this a bug in SQL Server? Is there way to get correct answer? Any help is appreciated. Thanks
这是SQL Server中的错误吗?有没有办法得到正确的答案?任何帮助表示赞赏。谢谢
2 个解决方案
#1
1
Try
SELECT SUM(PAYEES) AS SUM_PAYEES,
SUM(TOTAL) AS SUM_TOTAL,
cast(SUM(PAYEES) as decimal(18,10))/cast(SUM(TOTAL) AS DECIMAL(18,10)) AS DIVISION
FROM #ADMIN_FEE_1;
EDIT2:
tested Query
SELECT SUM(19940.00) AS SUM_PAYEES,SUM(59435.00) AS SUM_TOTAL,cast(SUM(19940.00) as decimal(18,10))/
cast(SUM(59435.00) as decimal(18,10)) AS DIVISION
#2
0
Try converting it to decimal
type like below
尝试将其转换为十进制类型,如下所示
SELECT SUM(PAYEES) AS SUM_PAYEES,
SUM(TOTAL) AS SUM_TOTAL,
CAST(SUM(PAYEES)/SUM(TOTAL) AS DECIMAL(12,10)) AS DIVISION
FROM #ADMIN_FEE_1;
#1
1
Try
SELECT SUM(PAYEES) AS SUM_PAYEES,
SUM(TOTAL) AS SUM_TOTAL,
cast(SUM(PAYEES) as decimal(18,10))/cast(SUM(TOTAL) AS DECIMAL(18,10)) AS DIVISION
FROM #ADMIN_FEE_1;
EDIT2:
tested Query
SELECT SUM(19940.00) AS SUM_PAYEES,SUM(59435.00) AS SUM_TOTAL,cast(SUM(19940.00) as decimal(18,10))/
cast(SUM(59435.00) as decimal(18,10)) AS DIVISION
#2
0
Try converting it to decimal
type like below
尝试将其转换为十进制类型,如下所示
SELECT SUM(PAYEES) AS SUM_PAYEES,
SUM(TOTAL) AS SUM_TOTAL,
CAST(SUM(PAYEES)/SUM(TOTAL) AS DECIMAL(12,10)) AS DIVISION
FROM #ADMIN_FEE_1;