-1

I am trying to calculate the percent of total using partition over, and need the result to calculate beyond 6 decimal places. I have millions of records, so I need the calculation to extend beyond 6 decimal places to sum to 100% of total.

I have tried to cast the result as decimal with 18 places, but I am still getting all zeros beyond the 6th decimal place.

Any help would be appreciated!

SELECT
     ISNULL(SUM(c.PaidOriginal),0)  / SUM(NULLIF(SUM(c.PaidOriginal),0) ) OVER (PARTITION BY c.calyr) AS percentoftotal
    ,CAST(ISNULL(SUM(c.PaidOriginal),0)  / SUM(NULLIF(SUM(c.PaidOriginal),0) ) OVER (PARTITION BY c.calyr) AS DECIMAL(38,18)) AS percentoftotal
FROM c

[screenshot of result]

1

Update: Thank you for the responses! I have tried casting everything as decimal (the numerator, denominator, and result) and am still getting all zeros past the 6th decimal place. Perhaps the issue is with the Partition by?

CAST(CAST(ISNULL(SUM(c.PaidOriginal),0) AS DECIMAL(38,18)) / CAST(SUM(NULLIF(SUM(c.PaidOriginal),0) ) OVER (PARTITION BY c.calyr)as decimal(38,18)) as decimal(38,18)) AS percentoftotal
Dale K
  • 25,246
  • 15
  • 42
  • 71
Heidi
  • 19
  • 1
  • 3
    tag your database , also what are datatype of your columns , plus add sample data – eshirvana May 19 '21 at 19:11
  • 1
    You're calling `CAST()` on the result of the division; after the division has already happened. This means that any rounding has already happened, the information you want to retain has already been lost. Try casting THEN dividing. `CAST(x AS DECIMAL(38,18)) / y` – MatBailie May 19 '21 at 19:14
  • What is the datatype/precision of `c.PaidOriginal`? – jmoerdyk May 19 '21 at 20:04
  • jmoerdyk the data type of c.PaidOriginal is numeric (dollars) – Heidi May 19 '21 at 20:18

2 Answers2

3

cast first or second number to the precision you want before deviding:

SELECT
     ISNULL(SUM(c.PaidOriginal),0)  / SUM(NULLIF(SUM(c.PaidOriginal),0) ) OVER (PARTITION BY c.calyr) AS percentoftotal
    ,CAST(ISNULL(SUM(c.PaidOriginal),0) AS DECIMAL(38,18)) / SUM(NULLIF(SUM(c.PaidOriginal),0) ) OVER (PARTITION BY c.calyr)  AS percentoftotal
FROM c

db<>fiddle here

eshirvana
  • 23,227
  • 3
  • 22
  • 38
  • I have tried casting the numerator, denominator, and result all as decimal and am still getting zeros beyond decimal 6. – Heidi May 19 '21 at 19:59
  • ,CAST(CAST(ISNULL(SUM(c.PaidOriginal),0) AS DECIMAL(38,18)) / CAST(SUM(NULLIF(SUM(c.PaidOriginal),0) ) OVER (PARTITION BY c.calyr)as decimal(38,18)) as decimal(38,18)) AS percentoftotal – Heidi May 19 '21 at 19:59
  • @Heidi , it works perfectly for me , see *db<>fiddle [here](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=d00097299a86be5e8dfbe392983acb9b)* – eshirvana May 19 '21 at 21:18
0

I had this same issue and finally was able to get it to work. I had to lower my precision. Everything else, as you mentioned, would stop after 6 and just add 0's. For Example

convert(numeric(30,8), PaidOriginal)
user3998848
  • 73
  • 1
  • 6