February 25, 2010 at 1:41 am
Hello Experts,
My database table and its data as follows:
CREATE TABLE TEST
(
lfisno int,
Stockcode varchar(50),
quantity int
-- rate float
)
INSERT INTO TEST values (1,'A',50)
go
INSERT INTO TEST values (1,'B',100)
go
INSERT INTO TEST values (2,'C',20)
go
INSERT INTO TEST values (2,'D',50)
go
INSERT INTO TEST values (2,'C',30)
go
I want to create a new database view that consist a new column named "Rate". Rate is the proportion of the Quantity columns for each lfisno.
My desired resultset is:
lfisno StockCode Quantity Rate
1 A 50 0.33
1 B 100 0.66
2 C 50 0.50
2 D 50 0.50
For example the rate value for the stockcode A is = 50/100=0.33
Please help
February 25, 2010 at 2:10 am
SELECT lfisno, Stockcode, quantity
,CAST(quantity * 1.0 / SUM(quantity) OVER (PARTITION BY lfisno) AS decimal(6,2))AS rate
FROM
(
SELECT lfisno, Stockcode
,SUM(quantity) AS quantity
FROM test
GROUP BY lfisno, Stockcode
) D
February 25, 2010 at 2:23 am
Here is one way:
with MyCTE as (
select lfisno, sum(quantity) as QuantityPerlfisno
from TEST
group by lfisno)
select T.lfisno, Stockcode, quantity, convert(real,quantity)/M.QuantityPerlfisno as rate
from TEST T inner join MyCTE M on T.lfisno = M.lfisno
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 25, 2010 at 4:38 am
Thank you very much for your replies.
I have solved the issue with your help..
February 25, 2010 at 6:20 am
erdem1973 (2/25/2010)
Thank you very much for your replies.I have solved the issue with your help..
Would you post your final code, please?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply