How to sum multiple columns with Group by in SQL Server?

Question

I have a table called tblEmployee. I would like sum the value and grouped by UserId.

tblEmployee:

+--------+----------+-------+
| UserId | UserName | Value |
+--------+----------+-------+
|      1 |    A     |   100 |
|      1 |    B     |   200 |
|      1 |    C     |   300 |
|      2 |    D     |   100 |
|      3 |    E     |   200 |
|      3 |    F     |   300 |
+--------+----------+-------+

I would like sum the value based on userid and shows like below output.

Output:

    +--------+----------+-------+---------+
    | UserId | UserName | Value | Result  |
    +--------+----------+-------+---------+
    |      1 |    A     |   100 |         |
    |      1 |    B     |   200 |  600    |
    |      1 |    C     |   300 |         |
    |      2 |    D     |   100 |  100    |
    |      3 |    E     |   200 |         |
    |      3 |    F     |   300 |  500    |
    +--------+----------+-------+---------+

How to do it?

TheGameiswar · Accepted Answer · 2016-06-18 12:54:25Z

4

select 
userid,username,
value,
sum(value) over (partition by userid) as totalval
from table

edited Jun 18, 2016 at 12:54

answered Jun 18, 2016 at 10:25

TheGameiswar

29k9 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2016-06-18 10:28:56Z

2

If you don't want to use a partition, you can also use a subquery with GROUP BY to get the output you want:

SELECT t1.UserId, t1.UserName, t1.Value, t2.Result
FROM tblEmployee t1
INNER JOIN
(
    SELECT UserId, SUM(Value) AS Result
    FROM tblEmployee
    GROUP BY UserId
) t2
ON t1.UserId = t2.UserId

answered Jun 18, 2016 at 10:28

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

1 Comment

Bogdan Sahlean Over a year ago

This works when tblEmployee.UserId column doesn't include NULLs.

Collectives™ on Stack Overflow

How to sum multiple columns with Group by in SQL Server?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related