Sum of partition-CodePudding

Suppose my table has below 3 columns:

Col1  Col2 Col3
2     A    A
3     B    A
4     C    A
4     D    B
5     E    B
6     F    B

Output

Col1 Col2 Col3
9    A    A
9    B    A
9    C    A
15   D    B
15   E    B
15   F    B

I want grouping based on column 3 and want to sum first 3 rows and they should show as 9,9,9 in output. I want to fetch all records from column 2 also.

Select Sum(Col1), Col2, Col3 from Table
Group by Col2, Col3

I have used group by and over (). Not able to get this output. Kindly let me know how this can be achieve.

CodePudding user response：

We can try using SUM() as an analytic function here:

SELECT SUM(Col1) OVER (PARTITION BY Col3) AS Col1, Col2, Col3
FROM yourTable
ORDER BY Col2;