# Calculate sum based on another column

• ### Question

• how to calculate sum of a column values based on another column value without creating view or function in a sql server.

EX:

Col 1 Col 2   Col 3 = Col2/Sum of same value from col1

A      1          1/3
A      2          2/3
B      3          3/12
B      4         4/12
B      5         5/12
C      6         6/13
C      7         7/13

any suggestions?

Thursday, May 30, 2013 9:47 PM

• pls try

```create table #t (c char(1),c1 int)
insert into #t(c,c1) values
('A',1),
('A',2),
('B',3),
('B',4),
('B',5),
('C',6),
('C',7)

select c,c1,c1*1.0/ sum(c1) over(partition by c)
from #t```

vt

• Marked as answer by Thursday, May 30, 2013 10:25 PM
Thursday, May 30, 2013 10:01 PM

### All replies

• pls try

```create table #t (c char(1),c1 int)
insert into #t(c,c1) values
('A',1),
('A',2),
('B',3),
('B',4),
('B',5),
('C',6),
('C',7)

select c,c1,c1*1.0/ sum(c1) over(partition by c)
from #t```

vt

• Marked as answer by Thursday, May 30, 2013 10:25 PM
Thursday, May 30, 2013 10:01 PM
• Thanks vt but I have only read access to this database, I cannot create any table, view or function
Thursday, May 30, 2013 10:08 PM
• thats just an e.g. code only, replace c and c1 with actual column name and #t with actual table name

• Marked as answer by Thursday, May 30, 2013 10:25 PM
• Unmarked as answer by Thursday, May 30, 2013 10:25 PM
Thursday, May 30, 2013 10:12 PM
• try this code ,

declare @tab table(id varchar , value int)
insert into @tab values('A',1)
insert into @tab values('A',2)

insert into @tab values('B',3)
insert into @tab values('B',4)
insert into @tab values('B',5)

insert into @tab values('C',6)
insert into @tab values('C',7)

select t1.id ,t2.value ,cast(t2.value as decimal)/cast(t1.SUM as decimal) from @tab t2  inner join
(select id , SUM(value) as SUM   from @tab group by id)  t1 on t1.id = t2.id

Thursday, May 30, 2013 10:17 PM
• Thanks Vt, was not aware of over clause and now that I know used it works perfect .
Thursday, May 30, 2013 10:28 PM
• Try this

CREATE TABLE #t (c CHAR(1),c1 INT)
INSERT INTO #t(c,c1) VALUES
('A',1),
('A',2),
('B',3),
('B',4),
('B',5),
('C',6),
('C',7)

;WITH c1 AS
(
SELECT c,SUM(c1) AS c1
FROM #t
GROUP BY c
)

SELECT c.c,t.c1,c.c1,CAST(t.c1 AS DECIMAL(8,2))/CAST(c.c1 AS DECIMAL(8,2)) as'%'
FROM c1 c
INNER JOIN #t t
ON c.c = t.c

Thursday, May 30, 2013 11:33 PM