|
|
You're in the
sql server programming
group:Summing rounded perventages to 100
sql server programming:
I have percentages that sums to 100%, but the business people wants to show the percentages with one decimal only. I understand why some numbers are rounded up, others down and why they don’t always add up to 100% afterwards. Does anyone have any good suggestions for a rounding algorithm that sums to 100? A set one would be fantastic. I’m running SQL2005, and I have included an example of my problem: DECLARE @Example TABLE ( val DECIMAL(19,6) ) INSERT INTO @Example ([val]) VALUES (83.285400) INSERT INTO @Example ([val]) VALUES (1.135000) INSERT INTO @Example ([val]) VALUES (0.037900) INSERT INTO @Example ([val]) VALUES (0.814600) INSERT INTO @Example ([val]) VALUES (4.421000) INSERT INTO @Example ([val]) VALUES (10.306100) SELECT [val] 'Val', ROUND([val],1) 'ValRounded' FROM @Example SELECT SUM([val]) 'SumVal', SUM(ROUND([val],1)) 'SumValRounded' FROM @Example -- Best regards
Mikael
SELECT SUM([val]) 'SumVal', FLOOR(SUM(ROUND([val],2))) 'SumValRounded' FROM @Example Also http://www.sqlmag.com/Articles/ArticleID/97032/97032.html?Ad=1 [quoted text, click to view] "Mikael" <Mikael@discussions.microsoft.com> wrote in message news:BD23325D-983B-4A78-8017-730D9DAC7888@microsoft.com... > Hi > I have percentages that sums to 100%, but the business people wants to > show > the percentages with one decimal only. I understand why some numbers are > rounded up, others down and why they don’t always add up to 100% > afterwards. > Does anyone have any good suggestions for a rounding algorithm that sums > to > 100? A set one would be fantastic. > I’m running SQL2005, and I have included an example of my problem: > > DECLARE @Example TABLE > ( > val DECIMAL(19,6) > ) > > INSERT INTO @Example ([val]) VALUES (83.285400) > INSERT INTO @Example ([val]) VALUES (1.135000) > INSERT INTO @Example ([val]) VALUES (0.037900) > INSERT INTO @Example ([val]) VALUES (0.814600) > INSERT INTO @Example ([val]) VALUES (4.421000) > INSERT INTO @Example ([val]) VALUES (10.306100) > > SELECT [val] 'Val', ROUND([val],1) 'ValRounded' > FROM @Example > > SELECT SUM([val]) 'SumVal', SUM(ROUND([val],1)) 'SumValRounded' > FROM @Example > > -- > Best regards > > Mikael
What do you want to do with the rounding error ?
You have to put it somewhere. Here's how to get it. select val,round(val,1),RoundError = val-round(val,1) from @Example select sum(val),sum(round(val,1)),sum(val-round(val,1)) from @Example Dave [quoted text, click to view] Mikael wrote:
> Unfortunately the result goes on the web page, so education on mathematics is > out of my hands. > The thing is that I need to show the percentages (the ValRounded column), > not the sum of the percentages. But I need the sum to be 100%. > > Val ValRounded > 83.2854 83.3 > 1.135 1.1 > 0.0379 0 > 0.8146 0.8 > 4.421 4.4 > 10.3061 10.3 > > Sums to: > 100 99.9
Ok , Heres my thoughts....
You need the sum to be 100 % but you have to round to 1 DP. This will cause rounding errors , which you have to do "something" with so that you will sum to 100%. As you cant create a "rounding error" row you need to place the missing value somewhere into the orignal values. So adding 0.1 back into the values for X many times, where X is the sum on the rounding error *10 , this should correct the "innacuracy" . Anyway , i think this works, please give it a lot of testing if you use it. DECLARE @Example TABLE ( [id] INT IDENTITY(1,1) PRIMARY KEY, [val] DECIMAL(19,6) ) INSERT INTO @Example ([val]) VALUES (83.285400) INSERT INTO @Example ([val]) VALUES (1.135000) INSERT INTO @Example ([val]) VALUES (0.037900) INSERT INTO @Example ([val]) VALUES (0.814600) INSERT INTO @Example ([val]) VALUES (4.421000) INSERT INTO @Example ([val]) VALUES (10.306100) declare @MaxAdj integer select @MaxAdj =sum(val-round(val,1))*10 from @Example select val,Rounded = round(val,1),RoundError = val-round(val,1),x=abs(val-round(val,1)), rownum = row_number() over (order by abs(val-round(val,1) ) ) from @Example select *,Corrected = rounded +case when rownum <=@MaxAdj then 0.1 else 0.0 end from ( select val,Rounded = round(val,1),RoundError = val-round(val,1),x=abs(val-round(val,1)), rownum = row_number() over (order by abs(val-round(val,1) ) ) from @Example ) as Derived Dave [quoted text, click to view] Mikael wrote:
Hi Uri
I need to display the percentages, not the sum, but I need it to sum to 100. I made his myself, but dont think it is pretty: (The reason why I wound like a set based version is that I have values for many products on different days in my resultset.) SET NOCOUNT ON DECLARE @Example TABLE ( [id] INT IDENTITY(1,1) PRIMARY KEY, [val] DECIMAL(19,6) ) INSERT INTO @Example ([val]) VALUES (83.285400) INSERT INTO @Example ([val]) VALUES (1.135000) INSERT INTO @Example ([val]) VALUES (0.037900) INSERT INTO @Example ([val]) VALUES (0.814600) INSERT INTO @Example ([val]) VALUES (4.421000) INSERT INTO @Example ([val]) VALUES (10.306100) WHILE (SELECT SUM(ROUND([val],1)) FROM @Example) < 100.000000 BEGIN PRINT '<' UPDATE e SET [val] = FLOOR([val]*10.000000)/10.000000 + 0.1 OUTPUT deleted.*,inserted.[val] FROM @Example e INNER JOIN ( SELECT TOP (1) [id] FROM @Example WHERE ([val] - FLOOR([val]*10)/10) < 0.05 ORDER BY ([val] - FLOOR([val]*10)/10) DESC, [val] DESC ) g ON e.[id] = g.[id] END WHILE (SELECT SUM(ROUND([val],1)) FROM @Example) > 100.000000 BEGIN PRINT '>' UPDATE e SET [val] = FLOOR([val]*10)/10 OUTPUT deleted.*,inserted.[val] FROM @Example e INNER JOIN ( SELECT TOP (1) [id] FROM @Example WHERE ([val] - FLOOR([val]*10)/10) > 0.05 ORDER BY ([val] - FLOOR([val]*10)/10) ASC, [val] DESC ) g ON e.[id] = g.[id] END SELECT [val] 'Val', ROUND([val],1) 'ValRounded' FROM @Example SELECT SUM([val]) 'SumVal', SUM(ROUND([val],1)) 'SumValRounded' FROM @Example -- Best regards Mikael [quoted text, click to view] "Uri Dimant" wrote:
> Mikael > SELECT SUM([val]) 'SumVal', FLOOR(SUM(ROUND([val],2))) 'SumValRounded' > > FROM @Example > > Also > > http://www.sqlmag.com/Articles/ArticleID/97032/97032.html?Ad=1 > > > > "Mikael" <Mikael@discussions.microsoft.com> wrote in message > news:BD23325D-983B-4A78-8017-730D9DAC7888@microsoft.com... > > Hi > > I have percentages that sums to 100%, but the business people wants to > > show > > the percentages with one decimal only. I understand why some numbers are > > rounded up, others down and why they don’t always add up to 100% > > afterwards. > > Does anyone have any good suggestions for a rounding algorithm that sums > > to > > 100? A set one would be fantastic. > > I’m running SQL2005, and I have included an example of my problem: > > > > DECLARE @Example TABLE > > ( > > val DECIMAL(19,6) > > ) > > > > INSERT INTO @Example ([val]) VALUES (83.285400) > > INSERT INTO @Example ([val]) VALUES (1.135000) > > INSERT INTO @Example ([val]) VALUES (0.037900) > > INSERT INTO @Example ([val]) VALUES (0.814600) > > INSERT INTO @Example ([val]) VALUES (4.421000) > > INSERT INTO @Example ([val]) VALUES (10.306100) > > > > SELECT [val] 'Val', ROUND([val],1) 'ValRounded' > > FROM @Example > > > > SELECT SUM([val]) 'SumVal', SUM(ROUND([val],1)) 'SumValRounded' > > FROM @Example > > > > -- > > Best regards > > > > Mikael > >
Wouldn't something like this be enough for presentation purposes:
SELECT SUM([val]) as SumVal ,ROUND(SUM([val]),1) as SumValRounded -- sum first, round later FROM @Example IMHO it would take less time to explain the basics of mathematics to your users than to come up with what might seem like a perfect rounding solution. ML --- Matija Lah, SQL Server MVP
Unfortunately the result goes on the web page, so education on mathematics is
out of my hands. The thing is that I need to show the percentages (the ValRounded column), not the sum of the percentages. But I need the sum to be 100%. Val ValRounded 83.2854 83.3 1.135 1.1 0.0379 0 0.8146 0.8 4.421 4.4 10.3061 10.3 Sums to: 100 99.9 -- Best regards Mikael [quoted text, click to view] "ML" wrote:
> Wouldn't something like this be enough for presentation purposes: > > SELECT SUM([val]) as SumVal > ,ROUND(SUM([val]),1) as SumValRounded -- sum first, round later > FROM @Example > > IMHO it would take less time to explain the basics of mathematics to your > users than to come up with what might seem like a perfect rounding solution. > > > ML > > --- > Matija Lah, SQL Server MVP
Have you actually tried my suggestion (summing before rounding)?
ML --- Matija Lah, SQL Server MVP
But I dont show the sum, just the percentages theirfor summing before
rounding doesent make sence to me. -- Best regards Mikael [quoted text, click to view] "ML" wrote:
> Have you actually tried my suggestion (summing before rounding)? > > > ML > > --- > Matija Lah, SQL Server MVP
I would like to have the percentage closest to being rounded in the favorable
direction do so, and if there are ties, the biggest number and with more ties, just a random one. This does the trick but I would realle like a set based version: (I do the positioning on multiple things and on multiple dates) WHILE (SELECT SUM(ROUND([val],1)) FROM @Example) < 100.000000 BEGIN PRINT '<' UPDATE e SET [val] = FLOOR([val]*10.000000)/10.000000 + 0.1 OUTPUT deleted.*,inserted.[val] FROM @Example e INNER JOIN ( SELECT TOP (1) [id] FROM @Example WHERE ([val] - FLOOR([val]*10.000000)/10.000000) < 0.05 ORDER BY ([val] - FLOOR([val]*10.000000)/10.000000) DESC, [val] DESC ) g ON e.[id] = g.[id] END WHILE (SELECT SUM(ROUND([val],1)) FROM @Example) > 100.000000 BEGIN PRINT '>' UPDATE e SET [val] = FLOOR([val]*10.000000)/10.000000 OUTPUT deleted.*,inserted.[val] FROM @Example e INNER JOIN ( SELECT TOP (1) [id] FROM @Example WHERE ([val] - FLOOR([val]*10.000000)/10.000000) > 0.05 ORDER BY ([val] - FLOOR([val]*10.000000)/10.000000) ASC, [val] DESC ) g ON e.[id] = g.[id] END -- Best regards Mikael [quoted text, click to view] "Dave Ballantyne" wrote:
> What do you want to do with the rounding error ? > You have to put it somewhere. Here's how to get it. > > > select val,round(val,1),RoundError = val-round(val,1) from @Example > select sum(val),sum(round(val,1)),sum(val-round(val,1)) from @Example > > Dave > > Mikael wrote: > > Unfortunately the result goes on the web page, so education on mathematics is > > out of my hands. > > The thing is that I need to show the percentages (the ValRounded column), > > not the sum of the percentages. But I need the sum to be 100%. > > > > Val ValRounded > > 83.2854 83.3 > > 1.135 1.1 > > 0.0379 0 > > 0.8146 0.8 > > 4.421 4.4 > > 10.3061 10.3 > > > > Sums to: > > 100 99.9 > >
What you're asking for is mathematically impossible, unless you want to
corrupt data. See Dave's post - what about the rounding error? Why would you prefer to mask it by mangling the data, rather than correctly informing the users of its existence. ML --- Matija Lah, SQL Server MVP
I agree, but this is my task :(
-- Best regards Mikael [quoted text, click to view] "ML" wrote:
> What you're asking for is mathematically impossible, unless you want to > corrupt data. See Dave's post - what about the rounding error? Why would you > prefer to mask it by mangling the data, rather than correctly informing the > users of its existence. > > > ML > > --- > Matija Lah, SQL Server MVP
This is Very Very nice :)
I will steal your logic, and try to apply it to some groupping and joining. Thanx Mikael [quoted text, click to view] "Dave Ballantyne" wrote:
> Ok , Heres my thoughts.... > > You need the sum to be 100 % but you have to round to 1 DP. > This will cause rounding errors , which you have to do "something" with > so that you will sum to 100%. > As you cant create a "rounding error" row you need to place the missing > value somewhere into the orignal values. So adding 0.1 back into the > values for X many times, where X is the sum on the rounding error *10 , > this should correct the "innacuracy" . > > Anyway , i think this works, please give it a lot of testing if you use it. > > DECLARE @Example TABLE > ( > [id] INT IDENTITY(1,1) PRIMARY KEY, > [val] DECIMAL(19,6) > ) > > INSERT INTO @Example ([val]) VALUES (83.285400) > INSERT INTO @Example ([val]) VALUES (1.135000) > INSERT INTO @Example ([val]) VALUES (0.037900) > INSERT INTO @Example ([val]) VALUES (0.814600) > INSERT INTO @Example ([val]) VALUES (4.421000) > INSERT INTO @Example ([val]) VALUES (10.306100) > > declare @MaxAdj integer > select @MaxAdj =sum(val-round(val,1))*10 from @Example > > > select val,Rounded = round(val,1),RoundError = > val-round(val,1),x=abs(val-round(val,1)), rownum = row_number() over > (order by abs(val-round(val,1) ) ) from @Example > > select *,Corrected = rounded +case when rownum <=@MaxAdj then 0.1 else > 0.0 end > from ( > select val,Rounded = round(val,1),RoundError = > val-round(val,1),x=abs(val-round(val,1)), rownum = row_number() over > (order by abs(val-round(val,1) ) ) from @Example > ) as Derived > > > Dave > > > > Mikael wrote: > > I agree, but this is my task :(
This one distributes the rounding error to all rows according to their value.
select CorrectedVal = round(val + (val * (sum(val) - sum(round(val, 1)))) / 100, 1) from @Example group by val ML --- Matija Lah, SQL Server MVP
I got:
0 0.8 1.1 4.4 10.3 83.3 And it sums to 99.9 I have the solution to my problem. Thank you anyways. -- Best regards Mikael [quoted text, click to view] "ML" wrote:
> This one distributes the rounding error to all rows according to their value. > > select CorrectedVal > = round(val > + (val * (sum(val) - sum(round(val, 1)))) / 100, 1) > from @Example > group by val > > > ML > > --- > Matija Lah, SQL Server MVP
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |