SQL unit value and cumulative sum by groups

Question

I'm still new to SQL and am kind of stumped on this one. I would greatly appreciate any help or advice. I have a table with a value column and an ID column that I then order by the value column in descending order. i.e:

Value   | ID   
12      | A  
09      | A  
08      | B  
08      | C  
07      | A  
06      | B  
03      | B  
01      | C  

I am trying to do two things:

1. For each row, calculate the percent of the total sum for its respective ID. Row 1 would be 12/(12+9+7), row 2: 3/(12+9+7), row 3: 8/(8+6+3), etc.
2. Calculate the running total of the percentage calculated in (1) for each ID. Essentially just a cumulative sum for each ID.

The output would look something like this.

Value | ID | UnitValue | RunningTotal  
-------------------------------------
  12  | A  | 0.43      | 0.43  
  09  | A  | 0.32      | 0.75  
  08  | B  | 0.47      | 0.47  
  08  | C  | 0.89      | 0.89  
  07  | A  | 0.25      | 1.00  
  06  | B  | 0.35      | 0.82  
  03  | B  | 0.18      | 1.00  
  01  | C  | 0.11      | 1.00  

Answer 1

For SQL Server 2008

;WITH CTE 
AS
(
    SELECT
        Value
        ,ID
        ,CONVERT(DECIMAL(10,2),Value/CONVERT(DECIMAL(10,2),SUM(Value) OVER(PARTITION BY [ID]))) AS [Unit Value]
    FROM
        Table1 

)

SELECT a.Value,a.ID,a.[Unit Value], (SELECT SUM(b.[Unit Value])
               FROM   CTE b
               WHERE a.ID = b.ID AND b.[Unit Value] <= a.[Unit Value]) AS [RunningTotal]
FROM   CTE a
ORDER  BY a.ID,[RunningTotal]

SQL FIDDLE DEMO

Answer 2

Try to use over() partition by () statement