基于键在表中求和记录

Question

表是一张大表格，数百万记录。

X列表示一个项目等表，椅子... ...Y列具有我想总结的值。如果需要求和该行，则Z将放置该值。

Table 1
ID     Column X    Column Y    Column Z   
1      X1          5           1
2      x1          4           1
3      x1          Null        0
4      x1          5           1
5      x1          Null        0
6      x2          5           1
7      x2          5           1
8      x2          Null        0
9      x3          2           1
10     x3          Null        0
11     x3          2           1
12     x4          Null        0
13     x4          Null        0
14     x5          Null        0
...  ...
the list goes on
Wanted Result
Table 1
ID     Column X    Column YY   Column Z   
1      X1          14          1
2      x1          14          1
3      x1          14          0
4      x1          14          1
5      x1          14          0
6      x2          10          1
7      x2          10          1
8      x2          10          0
9      x3          4           1
10     x3          4           0
11     x3          4           1
12     x4          0           0
13     x4          0           0
14     x5          0           0

我需要一个选择的语句才能获得预期的结果。

Answer 1

尝试此

Select ID,X,Sum(Y)Over(Partition By X) AS YY,Z
 From  Table

Answer 2

尝试：

SELECT 
    T1.ID, 
    T1.X,
    (
        SELECT 
            SUM(T2.Y*T2.Z) 
        FROM 
            Table AS T2 
        WHERE 
            T2.ID = T1.ID
    ) AS YY,
    T1.Z
FROM Table AS T1

Answer 3

比 @naveen @naveen。

with cte
(select ColumnX,sum(ColumnY) ColumnY from table1 group by ColumnX )
select a.id, a.ColumnX,b.ColumnY,a.ColumnZ from table1 a inner join cte b on a.ColumnX=b.ColumnX