Interest rate reference help for SQL Server based on a condition

Question

I have this query in temp table and i need help guys

Create Table ##TBL (ID INT, NOs INT,PersonCount INT)
INSERT INTO ##TBL VALUES
(0,0,36),
(0,0,10),
(2,1,30),
(2,2,35),
(2,2,32),
(3,0,24),
(4,0,18),
(4,2,9),
(4,0,33),
(4,3,45),
(5,0,21)
SELECT * FROM ##TBL

How can I get the result below for example, when the identifiers match, then I have to find a percentage of the maximum value of PersonCount. for example, where ID = 2, then the PersonCount column will be 35 / (30 + 35 + 32), that the maximum value of the PersonCount column is divided by the sum (PersonCount) and if this looks like ID = 5, then I should have 21/21 = 1.00

i should have this result

ID  NO  PersonCount Percent
0   0   36  0.78
2   2   35  0.36
3   0   24  1.00
4   3   45  0.43
5   0   21  1.00

Thanks a lot guys in advance Any question please let me know

Answer 1

Try the following:

SELECT ID, MAX( NOs ) NOs, 
    MAX( PersonCount ) m, 
    CAST( CAST( MAX( PersonCount ) AS DECIMAL(10,2) ) /  CAST( SUM( PersonCount ) AS DECIMAL(10,2) ) AS DECIMAL(10,2) ) x
FROM ##TBL
GROUP BY ID

Hope this is not your homework!