SQL calculate percentage - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL calculate percentage

Author

Topic

nizguy
Starting Member

37 Posts

Posted - 2009-10-29 : 18:32:34

Hello, Can anyone help me with this.

How to find the percentage of each individual weight.
I have a simple bill table:

BillNum as char 9,
Weight as int

And my sql query
Select BillNum, Weight
From Bill

Return
Bill# Weight
290180579 21014
290202843 1899
290559426 277
290750356 1432
290763900 292
290790720 320

I want to re-write my sql to return a new column Wt% as
Bill# Weight Wt%
290180579 21014 0.832765
290202843 1899 0.075256
290559426 277 0.010977
290750356 1432 0.056749
290763900 292 0.011572
290790720 320 0.012681

the Wt% is calculate as follow:
Wt% = individual weight / sum of all bills Weight
i.e sum(weight) = 25234
21014 / 25234 = 0.832765
1899 / 25234 = 0.075256
277 / 25234 = 0.010977
etc...

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-10-30 : 06:03:53

If u r using SQL2005 or later u can use[CODE]
SELECT BillNum, Weight, (Weight*1.0000)/SUM(Weight) OVER() AS "Wt%" FROM Bill[/CODE]if in 2000
[CODE]SELECT BillNum, Weight, (Weight*1.0000)/ (SELECT SUM(Weight) FROM Bill) AS "Wt%"
FROM Bill[/CODE]

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

nizguy
Starting Member

37 Posts

Posted - 2009-10-30 : 15:32:58

PeterNeo, It works great. thank you for you help!
I want to add one more column to my result set, (the "Revenue" column)
Revenue = sum(amount) * Wt%
i.e
6600 * .83276537 = 5496.25109
6600 * .075255608 = 496.6870096
6600 * .010977253 = 72.44986922
etc....

Is that possible? thank you

Bill#________Weight__amount__Wt%________Revenue
290180579__21014__6000__0.83276537___5496.25109
290202843__1899___500___0.075255608__496.6870096
290559426__277____100___0.010977253__72.44986922
290750356__1432___0000__0.056748831__374.5422842
290763900__292____0000__0.011571689__76.37314734
290790720__320____0000__0.012681303__83.69659983

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2009-10-30 : 16:04:27

Yes

, (sum(amount) * ( [SUBSTITUE FORMULA FROM ABOVE] )) as Revenue

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

nizguy
Starting Member

37 Posts

Posted - 2009-11-02 : 11:00:03

Thanks jhocutt,
I tried this and it gave me error. Is something wrong with my last query?

use Northwind
go

create table #testBill
(BillNum char(9),
Weight int,
Amount money)

select * from #testbill

insert #testbill (BillNum, Weight,Amount) VALUES ('290180579', 21014, 6000)
insert #testbill (BillNum, Weight,Amount) VALUES ('290202843', 1899, 500)
insert #testbill (BillNum, Weight,Amount) VALUES ('290559426', 277, 100)
insert #testbill (BillNum, Weight,Amount) VALUES ('290750356', 1432, 0)
insert #testbill (BillNum, Weight,Amount) VALUES ('290763900', 292, 0)
insert #testbill (BillNum, Weight,Amount) VALUES ('290790720', 320, 0)

select billNum, Weight, Amount, (Weight*1.0000) / (select sum(weight) from #testbill) as "Wt%"
from #testBill

select billNum, Weight, Amount,
(Weight*1.0000) / (select sum(weight) from #testbill) as "Wt%",
(sum(amount) * (Weight*1.0000) / (select sum(weight) from #testbill))
from #testBill

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2009-11-02 : 11:17:18

SELECT BillNum, Weight, (Weight*1.0000)/ a.SumWeight AS [Wt%] , SumAmount*((Weight*1.0000)/ a.SumWeight) as Revenue
FROM #testBill , (select sum(Weight) as SumWeight, Sum(Amount) as SumAmount from #testBill) a
go

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

nizguy
Starting Member

37 Posts

Posted - 2009-11-02 : 17:21:20

Work great! Thank you for both of your help.

nizguy
Starting Member

37 Posts

Posted - 2009-11-03 : 18:30:09

Hi Guys, I need more HELP on this query.
First, I add Zipcode char(5) column to #testBill table

create table #testBill
(
BillNum char(9),
Weight int,
Amount money,
Zipcode char(5)
)

insert #testbill (BillNum, Weight,Amount,Zipcode) VALUES ('290180579', 21014, 6000,'90012')
insert #testbill (BillNum, Weight,Amount,Zipcode) VALUES ('290202843', 1899, 500,'90012')
insert #testbill (BillNum, Weight,Amount,Zipcode) VALUES ('290559426', 277, 100,'90720')
insert #testbill (BillNum, Weight,Amount,Zipcode) VALUES ('290750356', 1432, 0,'90720')
insert #testbill (BillNum, Weight,Amount,Zipcode) VALUES ('290763900', 292, 0,'91395')
insert #testbill (BillNum, Weight,Amount,Zipcode) VALUES ('290790720', 320, 0,'91395')

Next, I create a new table #Area Cost

create table #AreaCost
(
Zipcode char(5),
Area char(1),
Min money,
Max money
)

insert #AreaCost (Zipcode, Area, Min, Max) VALUES ('90012','A', 9, 200)
insert #AreaCost (Zipcode, Area, Min, Max) VALUES ('90720','B', 9.50, 250)
insert #AreaCost (Zipcode, Area, Min, Max) VALUES ('91395','C', 10.50, 300.50)

AreaCost result
90012 A___9.0000__200.0000
90720 B___9.5000__250.0000
91395 C__10.5000__300.5000

SELECT BillNum, Weight, Amount, (Weight*1.0000)/ a.SumWeight AS [Wt%] , SumAmount*((Weight*1.0000)/ a.SumWeight) as Revenue, c.area
, (SumAmount*((Weight*1.0000)/ a.SumWeight)*.12) as Cost
FROM #testBill tb inner join #AreaCost c on tb.zipcode = c.zipcode, (select sum(Weight) as SumWeight, Sum(Amount) as SumAmount from #testBill) a
go

the result from this query
290180579__21014__6000.0000__.832765316636284__5496.2510897995__A__659.550130776
290202843__1899___500.0000__.075255607513672___496.6870095902___A__59.602441151
290559426__277____100.0000__.010977252912736___72.4498692241____B__8.693984307
290750356__1432___.0000_____.056748830942379___374.5422842197___B__44.945074106
290763900__292____.0000_____.011571688991043___76.3731473409____C__9.16477681
290790720__320____.0000_____.012681303003883___83.6965998256____C__10.043591979

how do I overwrite the Min/Max from the #AreaCost table if applied
I want the result look like this
290180579__21014__6000.0000__.832765316636284__5496.2510897995__A__200__(Should overwrite with 200 Max in area A #AreaCost table)
290202843__1899___500.0000__.075255607513672___496.6870095902___A__59.602441151
290559426__277____100.0000__.010977252912736___72.4498692241____B__9.50__(Should overwrite with 9.50 Min in Area B)
290750356__1432___.0000_____.056748830942379___374.5422842197___B__44.945074106
290763900__292____.0000_____.011571688991043___76.3731473409____C__10.50__(Should overwrite with 10.50 Min in Area C)
290790720__320____.0000_____.012681303003883___83.6965998256____C__10.50__(Should overwrite with 10.50 Min in Area C)

Appreciate your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources