SQL Server 2005: Scale of numeric expression changes when adding SUM()

Question

I have a table called "Jrl" with three columns:

code  AS varchar(15)
total AS numeric(13,2)
rem   AS numeric(13,4)

Let us assume for the sake of the argument, that there is just one row in the table with values '001', 400.00 and 52.1745.

Consider the following query:

SELECT code, total - rem
 FROM Jrl

It returns one row with '001' and 347.8255. This is correct.

If I change the query as follows (which is in fact the query I need in my code):

SELECT code, SUM(total) - SUM(rem)
 FROM Jrl
 GROUP BY code

It returns one row with '001' and 347.83 (that is, with scale 2 instead of 4).

Now according to the documentation at http://msdn.microsoft.com/en-us/library/ms190476%28v=sql.90%29.aspx, the type of the numeric expression (subtraction) should be numeric(16,4), which it obviously isn't. (I get the same behavior on SQL Server 2005 and 2008 R2.)

Can someone enlighten me as to what is happening there?

Btw. I did find a workaround, but I don't like it which is why I am posting this question. The workaround would be to add an explicit cast:

SELECT code, CAST(SUM(total) AS numeric(13,4)) - SUM(rem)
 FROM Jrl
 GROUP BY code

score 4 · Accepted Answer · edited May 23 '17 at 11:57

1) Please run this script and read my comments.

2) I hope this answer will help you.

3) The precision for SUM()-SUM() is 2 because you choose first to sum(SUM(total) and SUM(rem)) and then to subtract (SUM(total) - SUM(rem)).

4) My advise is to use SELECT t.code, SUM(t.total - t.rem) AS diff ... (first subtract and then SUM).

5) You may read my answer to this question SQL Numeric data type truncating value?:

DECLARE @Test TABLE(
    code  varchar(15),
    total numeric(13,2),
    rem   numeric(13,4)
);

INSERT  @Test (code, total, rem)
VALUES  ('001', 11.78, 5.6789);

--Test [1]
SELECT  dt.*,
        SQL_VARIANT_PROPERTY(dt.diff, 'BaseType') AS diff_BaseType,
        SQL_VARIANT_PROPERTY(dt.diff, 'Precision') AS diff_Precision,
        SQL_VARIANT_PROPERTY(dt.diff, 'Scale') AS diff_Scale
FROM
(
        SELECT  t.code, t.total - t.rem AS diff
        FROM    @Test t
) dt;

/*
Operation: e1 - e2
Result precision: max(s1, s2) + max(p1-s1, p2-s2) + 1 = max(2,4) + max(13-2, 13-4) + 1 = 4 + 11 + 1 = 16
Result scale: max(s1, s2) = max(2, 4) = 4
*/

--Test [2]
SELECT  dt.*,
        SQL_VARIANT_PROPERTY(dt.diff, 'BaseType') AS diff_BaseType,
        SQL_VARIANT_PROPERTY(dt.diff, 'Precision') AS diff_Precision,
        SQL_VARIANT_PROPERTY(dt.diff, 'Scale') AS diff_Scale
FROM
(
        SELECT  t.code, SUM(t.total - t.rem) AS diff
        FROM    @Test t
        GROUP BY t.code
) dt;

/*
Operation: SUM(e1 - e2)
Result precision: 38--For SUM function, I think (it's just a hipotese), SQL Server choose the maximum precision to prevent the overflow error
                    Argument:
                    DECLARE @t TABLE (Col NUMERIC(2,1)); INSERT @t VALUES (1);
                    SELECT  SQL_VARIANT_PROPERTY(SUM(t.Col), 'Precision') FROM @t t;
                    Result: precision = 38 (maximum DECIMAL/NUMERIC precision)
Result scale: the same scale as (e1-e2)= 4 (please see Test [1])
*/

--Test [3]
SELECT  dt.*,
        SQL_VARIANT_PROPERTY(dt.SUM_total, 'BaseType')  AS SUM_total_BaseType,
        SQL_VARIANT_PROPERTY(dt.SUM_total, 'Precision') AS SUM_total_Precision,
        SQL_VARIANT_PROPERTY(dt.SUM_total, 'Scale')     AS SUM_total_Scale,

        SQL_VARIANT_PROPERTY(dt.SUM_rem, 'BaseType')    AS SUM_rem_BaseType,
        SQL_VARIANT_PROPERTY(dt.SUM_rem, 'Precision')   AS SUM_rem_Precision,
        SQL_VARIANT_PROPERTY(dt.SUM_rem, 'Scale')       AS SUM_rem_Scale,

        SQL_VARIANT_PROPERTY(dt.diff, 'BaseType')       AS diff_BaseType,
        SQL_VARIANT_PROPERTY(dt.diff, 'Precision')      AS diff_Precision,
        SQL_VARIANT_PROPERTY(dt.diff, 'Scale')          AS diff_Scale
FROM
(
        SELECT  t.code, 
                SUM(t.total) AS SUM_total, SUM(t.rem) AS SUM_rem, SUM(t.total) - SUM(t.rem) AS diff
        FROM    @Test t
        GROUP BY t.code
) dt;

/*
Operation: SUM(total) (<> e1 + e2 + ...)
Result precision: 38--I think SQL Server choose the maximum precision to prevent the overflow error
Result scale: the same precision as total= 2
*/


/*
Operation: SUM(rem) (<> e1 + e2 + ...)
Result precision: 38--I think SQL Server choose the maximum precision to prevent the overflow error
Result scale: the same precision as rem= 4
*/

/*
Operation: SUM(total) - SUM(rem) = e1 - e2
Result precision: max(s1, s2) + max(p1-s1, p2-s2) + 1 = max(2,4) + max(38-2, 38-4) + 1 = 4 + 36 + 1 = 41 
but max. precision is 38 so result precision = 38

Calculated result scale: max(s1, s2) = 4 
but because the real precision for result (41) is greater than maximum precision (38)
SQL Server choose to decrease the precision of the result to 2 (please see Test [3] - diff_Scale).
In this case (the real precision for result is greater than maximum precision) I think the 
expression for result's precision is max(s1, s2) - (real precision - maximum precision) + 1 = 4 - (41 - 38) + 1 = 4 - 3 + 1 = 2
For example you could try to modify the definition of total column to `total numeric(13,1)` 
and you will see that the precision for SUM(total) - SUM(rem) becomes 4 - 4(4+37+1=42) + 1 = 1
*/

Results:

--Test [1] SELECT t.code, t.total - t.rem AS diff
code diff   diff_BaseType  diff_Precision diff_Scale
---- ------ -------------- -------------- ----------
001  6.1011 numeric        16             4

--Test [2] SELECT t.code, SUM(t.total - t.rem) AS diff
code diff   diff_BaseType diff_Precision diff_Scale
---- ------ ------------- -------------- ----------
001  6.1011 numeric       38             4

--Test [3] SELECT t.code, ..., SUM(t.total) - SUM(t.rem) AS diff
code SUM_total SUM_rem diff SUM_total_BaseType SUM_total_Precision SUM_total_Scale SUM_rem_BaseType SUM_rem_Precision SUM_rem_Scale diff_BaseType diff_Precision diff_Scale
---- --------- ------- ---- ------------------ ------------------- --------------- ---------------- ------------------------------- ------------- -------------- ----------
001  11.78     5.6789  6.10 numeric            38                  2               numeric          38                4             numeric       38             2

score 1 · Answer 2 · answered May 01 '12 at 09:33

1

thats because of the total AS numeric(13,2) field

since you are subtracting the sum of two fields with different precision, sql server presents the result with the precision of the smallest one.

If you do:

 create table jrl2(
code  varchar(15),
total numeric(13,4),
rem   numeric(13,4)
)
insert into jrl2 values ('001', 400.00 , 52.1745)

select * from jrl2
SELECT code, total - rem  FROM Jrl

SELECT code, SUM(total) - SUM(rem)
 FROM Jrl2
 GROUP BY code

you would get: 347.8255

answered May 01 '12 at 09:33

Diego

34,802
21
91
134

1

see the link Andreas provided; it says there that for `e1 - e2` operations the result scale will be `max(s1, s2)` – Cristian Lupascu May 01 '12 at 09:38
1

A footnote mentions that the scale is reduced if the maximum precision of 38 would be exceeded by the usual rules. That happens here because of SUM(). – Steve Kass May 01 '12 at 13:52
I saw that note but by the formula, the resulting precision should be 4 +max(11,9) + 1 = 16. how did u conclude it exceeded 38? – Diego May 01 '12 at 14:07

score 1 · Answer 3 · answered May 01 '12 at 13:50

Andreas,

The problem is that the return type of SUM() uses the maximum precision of 38. (See the "Return Types" section under SUM in Books Online: http://msdn.microsoft.com/en-us/library/ms187810%28v=sql.90%29.aspx.)

The type of your "total" column is numeric(13,2), so the result type of SUM(total) is (unfortunately) numeric(38,2). The scale of e1 + e2 is (again unfortunately) not max(s1,s2) when the precision of the operands is 38.

This is mentioned in a footnote in BOL: http://msdn.microsoft.com/en-us/library/ms190476.aspx. *The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

Also see http://support.microsoft.com/kb/281341.

SQL Server 2005: Scale of numeric expression changes when adding SUM()

3 Answers3