### Question

• I have a table like this:

Item                            QUAN
----------                       ---------
2                                   30
3                                  501
4                                  121
5                                    6
others                            20

Now I want to use SQL script to get a table like this:

Item                            percent
----------                       ---------
2                                   30/(30+501+121+6+20)
3                                  (501+30)/(30+501+121+6+20)
4                                  (121+501+30)/(30+501+121+6+20)
5                                  (6+121+501+30)/(30+501+121+6+20)

others                            20/(30+501+121+6+20)

Thanks.

btw, i want to a column, say percent as percent(%), i don't know how to write in SQL script neither. Please help. Thanks! :)

Monday, November 27, 2006 4:20 PM

### All replies

• I think this is what you want.  Notice that I create a table variable in code so that I could test your data.  In your final code, you'll want to remove the table variable stuff (@Temp) and use your actual table instead.  I hope this helps.

Declare @Temp Table (Item VarChar(20), Quan int)

Insert Into @Temp Values('2',30)
Insert Into @Temp Values('3',501)
Insert Into @Temp Values('4',121)
Insert Into @Temp Values('5',6)
Insert Into @Temp Values('others',20)

Declare @Quantity Int
Set @Quantity = 0

Select  Item, Case When IsNumeric(Item) = 1
Then 1.0 * CumulativeQuantity
Else Quan
End  / (Select Sum(Quan) From @Temp) As [Percent]
From (
Select  Item,
Quan,
@Quantity + (Select Sum(Quan) From @Temp A Where A.Item <= B.Item) As CumulativeQuantity
From  @Temp B
) As A

Monday, November 27, 2006 6:31 PM
• I don't want to choose a table which created by another SQL script, which make my script too complicated to read. is there any other solution? Let me put my problem again:

i want to change this table:

item   quan
-----  -----
1      10
2      24
3      45
4      23
5      11

to:

item quanlity
----- ----------
1      10
2      10+24
3      10+24+45
4      10+24+45+23
5      10+24+45+23+11

i has the following code:

SELECT item,

,CASE WHEN item in '1' THEN quan

WHEN item in '2' THEN quan + decode(item,'1',quan)

WHEN item in '3' THEN quan + decode(item,'2',quan)

WHEN item in '4' THEN quan + decode(item,'3',quan)

WHEN item in '5' THEN quan + decode(item,'4',quan)

END quanlity

Tuesday, November 28, 2006 2:26 AM
• Hello Mr. Wang:
You question is a running total issue. You can do something like this:

SELECT t1.item, (SELECT Sum(t2.Quan)
FROM  runingTotal t2 WHERE t2.item<=t1.item) as totalQuantity
FROM  runingTotal t1
Let me know if you have more question.
Tuesday, November 28, 2006 4:54 AM
• Thanks guys, because I use ORACLE database, so I tried to use the following analysis functions to solve this problem:

case when item < 7 then sum(quan) over(PARTITION by null
order by item range unbounded preceding)/sum(quan)
else null
end "PERCENT(%)"

Thanks. :)

Tuesday, November 28, 2006 7:37 AM