CODEWITHSUNDEEP
sqlsql-servert-sql
Pranay
Pranay

Reputation: 430

How to show "value"[column] and "sum of all such values"[row wise] for a group of EmpId in the same row

How to show "value"[column] and "sum of all such values"[row wise] for a group of EmpId in the same row.

SELECT ID, SUM(VALUE1), SUM(VALUE2) 
GROUP BY EmpID

The above query will return the sum, but I also want to show values of Value1 and Value2 in the same row.

E.g.

I have following input table:

EmpID  VALUE1  VALUE2
==================
1       1       5         
1       2       6         
2       3       7         
2       4       8

I want following output table (grouped by EmpID) -> Both Value1, Value2 and their sum in same row

EmpID  Value1  VALUE2  total_Value1   total_Value2
===============================================
1       1       5         3               11
1       2       6         3               11
2       3       7         7               15     
2       4       8         7               15

Upvotes: 0

Views: 55

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270763

Use window functions:

SELECT ID, SUM(SUM(VALUE1)) OVER (PARTITION BY id),
       SUM(SUM(VALUE2)) OVER (PARTITION BY id)
FROM T
GROUP BY EmpID;

However, I don't think aggregation is needed:

SELECT ID, SUM(VALUE1) OVER (PARTITION BY id),
       SUM(VALUE2) OVER (PARTITION BY id)
FROM T;

Upvotes: 2

Arulkumar
Arulkumar

Reputation: 13237

Using SUM() OVER(PARTITION BY) window function, you can get your expected result:

SELECT EmpID, VALUE1, VALUE2,
       SUM(VALUE1) OVER (PARTITION BY EmpID) AS total_Value1,   
       SUM(VALUE2) OVER (PARTITION BY EmpID) AS total_Value2
FROM TableName

Demo on db<>fiddle

Upvotes: 4

Related Questions