SQL Server - 除以列的总和

时间:2021-03-13 22:29:18

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;