How does SQL Server handle decimal precision when the SUM() function is called? For precision and scale I have been using this guide to help me determine what is going on. The query goal is to basically have a query which calculates the average price and quantity down to 8 decimal places. I can't change existing column datatypes.
SELECT
ROUND(SUM( CAST(Qty * Price AS DECIMAL(18,2))) / SUM(Qty),8,1)
Qty is defined as DECIMAL(24, 10).
Price is defined as DECIMAL(24, 10).
The reason I have the dividend cast down to DECIMAL(18, 2) is because I need it as a money value. For the division the math works fine if I do DECIMAL(18, 2) / DECIMAL(24, 10) it should return a DECIMAL (38, 12), but I see that my math is rounding down to 6 decimal points.
Here's some data I am using:
Qty | Price | TotalValue
-----+----------+------------
550 | 239.4101 | 131675.56
100 | 238.5528 | 23855.28
500 | 235.2 | 117600
---------------------------
1150 | | 273130.84
So I should be getting 273130.84/1150 = 237.50507826 but my query cuts off the last two decimal places. Does that mean I'm getting overflow somewhere in the scale so it's defaulting to (38,6)? The best solution I was able to do was cast again on the dividend side.
CAST(SUM( CAST(Qty * Price AS DECIMAL(18,2))) AS DECIMAL(18,2)) / SUM(Qty)
*Note: My original problem query is off. I have fixed it
Nevertheless @ThomA has a point - at least one of your queries seems to return what you expect, maybe some comments would be helpful (well, at least this exercise was interesting for myself).
How does SQL Server handle decimal precision when the SUM() function is called?
As the docs say, if the input was DECIMAL then the output precision is always 38.
Does that mean I'm getting overflow somewhere in the scale so it's defaulting to (38,6)?
Yes, in some cases it may fallback to (38,6). In the docs you mentioned above it's the third scenario for multiplication and division cases.
Let's say we are computing sum(qty*price) / sum(qty) with no explicit conversions.
When you multiply qty * price both of type (24,10) the output type will be
p = p1 + p2 + 1 = 24 + 24 + 1 = 49
s = s1 + s2 = 10 + 10 = 20
integral part = p - s = 49 - 20 = 29
Precision cannot be greater than 38, so in the final type it will be 38, not 49. The integral part here is 29 which is less than 32, thus it's the first scenario for multiplication and division and scale becomes
s = min(s, 38-(p-s)) = min(20, 38-(49-20) = min(20, 9) = 9
so the outcome of multiplication goes as (38,9). Even if it had precision less than 38, the SUM function would raise it to 38 anyways.
The divisor is sum(qty) where qty is of type (24,10) but the SUM(qty) output will be:
p = 38
s = original s
which is (38,10). So we are dividing (38,9) / (38,10). The output type of this equation will be:
p = p1 - s1 + s2 + max(6, s1 + p2 + 1) = 38 - 9 + 10 + max(6, 9 + 38 + 1) = 39 + 48 = 87
s = max(6, s1 + p2 + 1) = max(6, 9 + 38 + 1) = 48
integral part = 87 - 48 = 39
the integral part is 39 (> 32), and the scale is 48 (> 6), thus it's the third scenario, scale is set as 6 with no options and we get (38, 6). So, yes, there may occur some rounding if there is not enough space for storing the integral part; or the arithmetic overflow error will be raised. But explicit casting may prevent unexpected scale loss.
Here is db fiddle with some formula variations.
For the query
SELECT SUM( CAST(Qty * Price AS DECIMAL(18,2))) / SUM(Qty)
it will be (38, 2) / (38, 10) - only input for the first SUM is different. CAST inside SUM affects dividend scale only. Here are output type computations:
p = p1 - s1 + s2 + max(6, s1 + p2 + 1) = 38 - 2 + 10 + max(6, 2 + 38 + 1) = 46 + 41 = 87
s = max(6, s1 + p2 + 1) = max(6, 2 + 38 + 1) = 41
integral part = 87 - 41 = 46
the integral part is 46 (> 32), and the scale is 41 (> 6), thus it's the third scenario again - output type is (38, 6). That's why you "loose" last two digits. Output precision and scale of SUM of multiplication should be adjusted to prevent scale loss of the formula result.
And thanks to @ThomA - I did not know about sys.dm_exec_describe_first_result_set.