SQL, sum one column where another column is the same and display as the third column

Question

I have the following data:

a  b
1  23
1  14
1  3
2  45
2  16

and what I would like is to sum column b where column a is the same and have this displayed in a third column, like so:

a  b  c
1  23 40
1  14 40
1  3  40
2  45 61
2  16 61

Thanks.

Answer 1

You didn't specify your RDBMS, but here is a solution using SQL Server

Declare @T table (a int, b int)
INSERT INTO @T
    (a, b)
VALUES
    (1, 23),
    (1, 14),
    (1, 3),
    (2, 45),
    (2, 16)
;

select 
    a,b,sum(b) over (partition by a)  as c
from @T

---

| a | b  | c  |
|---|----|----|
| 1 | 23 | 40 |
| 1 | 14 | 40 |
| 1 | 3  | 40 |
| 2 | 45 | 61 |
| 2 | 16 | 61 |

---

If your RDBMS doesn't support Window Functions, you could use a common table expression to do the sum/group and then join that cte to the original table. The results are the same.

;WITH _cte
AS (
    SELECT a
        ,sum(b) AS c
    FROM @T
    GROUP BY a
    )
SELECT t.a
    ,t.b
    ,c.c
FROM @T t
JOIN _cte c ON c.a = t.a