# SQL percentage column

### Question

• Hi,

I'm trying to setup a table that will allow my user to apply weightings to a value to calculate how much an item might cost. my table currently is setup as:

```ID int,
WeightTableId int
GroupAId int
GroupBId int
Weight numeric(20,10)
﻿﻿﻿﻿﻿Measuer numeric(20,10)
PercentageSplit numeric(20,10)```

So in the table the user would add lines like

1                    A                A               1          8153

1                    A                B               1          94491

If I do this in excel its 8153+94491 to get the total weight then

A,A Split = 8153/102644 SQL:0.0794290000 Excel:0.079429874

A,B Split = 94491/102644 SQL:0.9205700000 Excel:0.920570123

The value in Excel adds up to 1 or 100% where as the SQL value adds to 0.999999 this is causing my amounts to lose .000001 which is affecting my total. Is there a way I can get the calculation to give me the more actuate value?

this is my current query

```UPDATE    TableDetail
SET              ProcessingSplit = (TableDetail.Weight * TableDetail.Measure)  / TotalWeight.TotalWeight
FROM         TableDetail INNER JOIN
(SELECT     TableHeaderId, SUM(Weight * Measure) AS TotalWeight
FROM          TableDetail AS TableDetail_1
WHERE     (TotalWeight.TotalWeight <> 0)```

• Edited by Tuesday, July 16, 2013 4:01 PM
Tuesday, July 16, 2013 3:58 PM

• Without going into "who is wrong", I observe an unnecessary problem in your design: the numeric columns are oversized. I don't know enough about "Measure" to say with any certainty that you don't need to store values up to 9 billion and need up to 10 decimals, but for "Weight" and "PercentageSplit" I am quite certain that numeric(20,10) is way too big.

The disadvantage of using oversized decimals is that you loose precision when you multiply or divide, as demonstrated in the code below.

```SET NOCOUNT ON
CREATE TABLE #oversized
(weight           decimal(20,10)
,measure          decimal(20,10)
,percentage_split decimal(20,10)
)
INSERT INTO #oversized VALUES (1,  8153, NULL)
INSERT INTO #oversized VALUES (1, 94491, NULL)

UPDATE #oversized
SET percentage_split = (weight * measure) / (
SELECT SUM(weight * measure)
FROM #oversized
)

SELECT * FROM #oversized

DROP TABLE #oversized

CREATE TABLE #properlysized
(weight           decimal(6,3)
,measure          decimal(20,10)
,percentage_split decimal(11,10)
)
INSERT INTO #properlysized VALUES (1,  8153, NULL)
INSERT INTO #properlysized VALUES (1, 94491, NULL)

UPDATE #properlysized
SET percentage_split = (weight * measure) / (
SELECT SUM(weight * measure)
FROM #properlysized
)

SELECT * FROM #properlysized

DROP TABLE #properlysized
SET NOCOUNT OFF

```

results

```weight                                  measure                                 percentage_split
--------------------------------------- --------------------------------------- ---------------------------------------
1.0000000000                            8153.0000000000                         0.0794290000
1.0000000000                            94491.0000000000                        0.9205700000

weight                                  measure                                 percentage_split
--------------------------------------- --------------------------------------- ---------------------------------------
1.000                                   8153.0000000000                         0.0794298741
1.000                                   94491.0000000000                        0.9205701259

```

Gert-Jan

• Marked as answer by Sunday, July 28, 2013 2:39 AM
Tuesday, July 16, 2013 6:56 PM

### All replies

• Technically SQL Server is correct, and Excel is wrong.  This is the difference between fixed decimal math and floating point math.  Excel uses floating point, which is an approximation of the number.

The significant digits makes the correct value 0.999999.

Tuesday, July 16, 2013 4:12 PM
• Try this in your subquery:

`CONVERT(INT,SUM(Weight * [﻿﻿﻿﻿﻿Measure])`

﻿

Tuesday, July 16, 2013 4:32 PM
• You can force SQL to return the same results by casting the numbers to float.

```UPDATE    TableDetail
SET              ProcessingSplit = (TableDetail.Weight * TableDetail.Measure)  / TotalWeight.TotalWeight
FROM         tabledetail as tabledetail INNER JOIN
(SELECT     TableHeaderId, CAST(SUM(Weight * Measure) AS float) AS TotalWeight
FROM          TableDetail AS TableDetail_1
WHERE     (TotalWeight.TotalWeight <> 0)```

• Marked as answer by Tuesday, July 16, 2013 5:08 PM
• Unmarked as answer by Sunday, July 28, 2013 2:39 AM
Tuesday, July 16, 2013 4:51 PM
• Hi Thanks,

this seems to mostly fix the issue will look at it in more detail tonight :)

Thanks

Tuesday, July 16, 2013 5:09 PM
• You can do the calculation using the float datatype.  It will help here, but maybe not always.  SQL Server and Excel have different rules for calculations, and don't always produce the same result.  But

```UPDATE    #FooNumeric
SET              PercentageSplit = (Cast(#FooNumeric.Weight As float) * #FooNumeric.Measure)  / TotalWeight.TotalWeight
FROM         #FooNumeric INNER JOIN
(SELECT     WeightTableId, SUM(Cast(Weight As float) * Measure) AS TotalWeight
FROM          #FooNumeric AS TableDetail_1
GROUP BY WeightTableId) AS TotalWeight ON #FooNumeric.WeightTableId = TotalWeight.WeightTableId
WHERE     (TotalWeight.TotalWeight <> 0)```

Tom

Tuesday, July 16, 2013 5:31 PM
• ```Merge TableDetail as target
Using
(SELECT     WeightTableId, SUM(Cast(Weight As Numeric(20,10)) * Measure) AS TotalWeight
FROM TableDetail AS d GROUP BY WeightTableId) AS Source
ON Target.WeightTableId = Source.WeightTableId
WHEN Matched AND  (Source.TotalWeight <> 0)
Then Update
Set  Target.PercentageSplit = (Cast(Target.Weight As  Numeric(20,10)) * Target.Measure)  / Source.TotalWeight ;```

Tuesday, July 16, 2013 6:48 PM
• Without going into "who is wrong", I observe an unnecessary problem in your design: the numeric columns are oversized. I don't know enough about "Measure" to say with any certainty that you don't need to store values up to 9 billion and need up to 10 decimals, but for "Weight" and "PercentageSplit" I am quite certain that numeric(20,10) is way too big.

The disadvantage of using oversized decimals is that you loose precision when you multiply or divide, as demonstrated in the code below.

```SET NOCOUNT ON
CREATE TABLE #oversized
(weight           decimal(20,10)
,measure          decimal(20,10)
,percentage_split decimal(20,10)
)
INSERT INTO #oversized VALUES (1,  8153, NULL)
INSERT INTO #oversized VALUES (1, 94491, NULL)

UPDATE #oversized
SET percentage_split = (weight * measure) / (
SELECT SUM(weight * measure)
FROM #oversized
)

SELECT * FROM #oversized

DROP TABLE #oversized

CREATE TABLE #properlysized
(weight           decimal(6,3)
,measure          decimal(20,10)
,percentage_split decimal(11,10)
)
INSERT INTO #properlysized VALUES (1,  8153, NULL)
INSERT INTO #properlysized VALUES (1, 94491, NULL)

UPDATE #properlysized
SET percentage_split = (weight * measure) / (
SELECT SUM(weight * measure)
FROM #properlysized
)

SELECT * FROM #properlysized

DROP TABLE #properlysized
SET NOCOUNT OFF

```

results

```weight                                  measure                                 percentage_split
--------------------------------------- --------------------------------------- ---------------------------------------
1.0000000000                            8153.0000000000                         0.0794290000
1.0000000000                            94491.0000000000                        0.9205700000

weight                                  measure                                 percentage_split
--------------------------------------- --------------------------------------- ---------------------------------------
1.000                                   8153.0000000000                         0.0794298741
1.000                                   94491.0000000000                        0.9205701259

```

Gert-Jan

• Marked as answer by Sunday, July 28, 2013 2:39 AM
Tuesday, July 16, 2013 6:56 PM