Clever Geek Handbook

Create a Column Sum values ​​from another column in one view

View: 

A  | B
10   1
15   2
12   3
5    2
2    1
2    1

Output View: 

A | B | C
10  1   14
15  2   20
12  3   12 
5   2   20
2   1   14
2   1   14

I need to sum the values ​​from Column A based on Column B. So, all the values ​​from Column B that have a value of 1 retrieve the values ​​from Column A and then sum it to Column C.

+3


source to share


2 answers


I don't see the point, but:



SELECT t.a
    ,t.b
    ,sumtab.c
FROM [yourtable] t
INNER JOIN (
    SELECT t.b
        ,sum(t.a) AS C
    FROM [yourtable] t
    GROUP BY t.b

    ) AS sumtab
    ON t.b = sumtab.b
0


source


You can use SUM() OVER

like this

DECLARE @SampleData AS TABLE
(
    A int,
    B int
)

INSERT INTO @SampleData
(
    A,
    B
)
VALUES
( 10, 1),
( 15, 2),
( 12, 3),
( 5 , 2),
( 2 , 1),
( 2 , 1)

SELECT *,
       sum(sd.A) OVER(PARTITION BY sd.B) AS C
FROM @SampleData sd


Returns

A   B   C
-----------
10  1   14
2   1   14
2   1   14
15  2   20
5   2   20
12  3   12
0


source






More articles:


All Articles