Reputation: 181
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
Reputation: 28860
select
userid,username,
value,
sum(value) over (partition by userid) as totalval
from table
Upvotes: 4
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