view, sum help

[yostyid]

I am trying to create a view with 3 col's. I need to find a way to sum column "b", but only for rows that have the same value in column "a" ie:
a b sum
1 3 5
1 2 5
2 1 3
2 2 3

I cant figure out how the query will work for that. I am figuring a subquery, but any help or direction would be great.

 

[checkai]

can't you do a group by

select a,sum(b)
from table
group by a

dlc

 

[yostyid]

I believe group by will collapse this example from 4 tuples to 2. I need all 4 to show.

 

[checkai]

I see...you could create a stored procedure that creates a #Table updates it and then select the rows...not sure how big of a table this is though...

dlc

 

[sunila7]

Try this

SELECT a, b, (SELECT SUM(b) FROM TBL t2 where t2.a =t1.a and t2.b=t2.b) SM
FROM TBL t1
Order by a,b


Sunil

 

[yostyid]

Thanks for your help checkai. I think Sunila7 has just what I was thinking about. Thanks!