CODEWITHSUNDEEP
sql-servermathdivision
Fahad Mullaji
Fahad Mullaji

Reputation: 1422

SQL Server - Divide sum of columns

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.

Data type of PAYEE and TOTAL is decimal(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

Upvotes: 0

Views: 9939

Answers (2)

A_Sk
A_Sk

Reputation: 4630

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

Upvotes: 2

Rahul
Rahul

Reputation: 77876

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;

Upvotes: 0

Related Questions