CODEWITHSUNDEEP
sql-server
dhamo
dhamo

Reputation: 181

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

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?

Upvotes: 2

Views: 6667

Answers (2)

TheGameiswar
TheGameiswar

Reputation: 28860

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

Upvotes: 4

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522824

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

Upvotes: 2

Related Questions