June 1, 2015 at 11:44 am
First of all sorry if this post shouldn't be here... I'm new here so I'm not sure where to place it
Well this is the issue I'm having.
I Got this table.
Table:
User ID : 1 2 1 1 2 2
rID: Null Null 1 2 1 4
Score: 100 100 -20 -30 -20 -40
and want to use a SUM Function to get the Score for each one of the user and separates them by rID.. basically I want to get this view.
UserID: 1 1 2 2
rID: 1 2 1 4
Score: 80 70 80 60
Any help would be appreciated!
June 1, 2015 at 11:57 am
Please give us create table statements and inserts to set up the data the way you list it. Then we can give you a statement to reproduce your desired results. Help us help you!
Also, it seems that you have an un-stated rule that NULL should be converted to 1?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 1, 2015 at 1:54 pm
Since you are completely new to the site I'll throw you a bone. 😉
Just for future reference people here are very helpful and always ready to help. However, like the previous poster mentioned helping us help you goes a long way.
So what you want to do first in your post is give us some code like this.
DECLARE @myTable TABLE (UserID INT, rID INT, Score INT)
INSERT INTO @myTable(UserID, rID, Score)
VALUES
(1, NULL, 100),
(2, NULL, 100),
(1, 1, -20),
(1, 2, -30),
(2, 1, -20),
(2, 4, -40)
SELECT * FROM @myTable
This lets us quickly copy and paste it in so we can come up with a solution.
Now that we got that out of the way your description is a little vague. Getting the SUM of values is easy...you can do something like this.
SELECT
UserID,
SUM(Score) AS TotalScore
FROM
@myTable
GROUP BY
UserID
That being said you specified you also wanted it grouped by rID as well. We have no context but if you were to do that then you would get essentially the same thing as if you just did a SELECT on the table. Reason for that is the combined values for UserID & rID in your example are completely unique.
SELECT
UserID,
rID,
SUM(Score) AS TotalScore
FROM
@myTable
GROUP BY
UserID,
rID
Therefore a little more detail would be great.
Cheers!
June 1, 2015 at 2:47 pm
This makes no sense for me, but using the sample data previously posted, I can get the expected results with 2 options (none of them use SUM function).
SELECT a.UserID,
a.rID,
a.Score + b.Score AS Score
FROM @myTable a
JOIN @myTable b ON a.UserID = b.UserID
WHERE a.rID IS NOT NULL
AND b.rID IS NULL;
WITH CTE(UserID, rID, Score) AS(
SELECT a.UserID,
a.rID,
a.Score + FIRST_VALUE( a.Score) OVER(PARTITION BY UserID ORDER BY rID ROWS UNBOUNDED PRECEDING) AS Score
FROM @myTable a
)
SELECT *
FROM CTE
WHERE rID IS NOT NULL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply