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.
| Author |
Topic |
|
vjay
Starting Member
2 Posts |
 Posted - 2002-06-27 : 13:42:18
|
| Hi, I have a table named 'test' with columns (storeid,sales).I need to calculate Ratio of each store with the total sales from all stores. For example, if i have the data like A1 500 , A2 200 , A3 300 i need the output like A1 500 05, A2 200 0.2 , A3 300 0.3 I need to make sure that the 'Ratio' is rounded to 3 decimal places and sum of the Ratio should be exactly 1 in any case. Thanks for your help.Vjay |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-27 : 14:09:48
|
| [rant]Always post DDL ... [/rant]create table #test( storeID char(2), sales money)insert #test( storeID, sales ) select 'A1', 500.00 union select 'A2', 200.00 union select 'A3', 300.00select storeID, cast(sales / (select sum(sales) from #test) as real) as ratiofrom #test tJonathan Boott, MCDBA |
 |
|
|
macka
Posting Yak Master
162 Posts |
Posted - 2002-06-27 : 14:12:33
|
| This should get you started,select storeid,sales,cast(sales as decimal)/(select sum(sales) from test)from testmacka. |
|
|
|
|
|
|
|
|
Ratio calculation