CODEWITHSUNDEEP
sqlsql-server
Peter Morris
Peter Morris

Reputation: 23224

SQL SUM() by value of another column

I am creating a generic key/value data store in MS SQL Server. I have a UserDecimalData table defined as follows

create table [UserDecimalData] (
    [UserID] nvarchar(64) not null,
    [ValueKey] uniqueidentifier not null,
    [Value] decimal null
);
alter table [UserDecimalData]
    add constraint PK_UserDecimalData primary key clustered ([UserID], [Date], [ValueKey]);

and as an example, here is some data that uses a GUID ending with 0001 to indicate an individual's score in a game, and a GUID ending with 0002 to indicate which team a user is on.

UserID  ValueKey                                Value
Dave    00000000-0000-0000-0000-000000000001    35
Dave    00000000-0000-0000-0000-000000000002    1
Phil    00000000-0000-0000-0000-000000000001    35
Phil    00000000-0000-0000-0000-000000000002    1
Pete    00000000-0000-0000-0000-000000000001    35
Pete    00000000-0000-0000-0000-000000000002    2

I can easily find the score for an individual by searching for their UserID + ValueKey ending with 0001. I can also find out which team an individual is in by searching for their UserID + the value of the row where the ValueKey ends with 0002.

My question is, how can I get a list of team scores? In this case team 1 would have 70 and team 2 would have 35.

Upvotes: 0

Views: 99

Answers (4)

Paul B
Paul B

Reputation: 26

select u1.Value, sum(u0.Value)
from UserDecimalData u1
    left join UserDecimalData u0 on u0.UserID = u1.UserID and u0.ValueKey like '%1' 
where u1.ValueKey like '%2'
group by u1.Value 

GO

Upvotes: 0

Paul B
Paul B

Reputation: 26

--first way
with commands as (
    select UserID as UserID, Value as command
    from UserDecimalData
    where ValueKey like '%2'
)
select c.command, sum(Value)
from UserDecimalData u
    left join commands c on u.UserID = c.UserID
where ValueKey like '%1'
group by command
GO

--second way
select c.command, sum(Value)
from UserDecimalData u
    left join (
            select UserID as UserID, Value as command
            from UserDecimalData
            where ValueKey like '%2'
        ) c on u.UserID = c.UserID
where ValueKey like '%1'
group by command
GO

--results
--1 70
--2 35

Upvotes: 0

Peter Morris
Peter Morris

Reputation: 23224

select v1.Value as Team, sum(v2.value) as TeamScore 
from userdecimaldata v1
join userdecimaldata v2 on v2.UserId = v1.UserId and v2.[Date] = v1.[Date]
where v1.ValueKey = @clankey and v2.ValueKey = @scoreKey
group by v1.Value

OUTPUT

 Team   TeamSccore
    1   70
    2   35

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Here is one method:

select sum(udd.value)
from UserDecimalData udd
where udd.ValueKey like '%1' and
      exists (select 1
              from UserDecimalData udd2
              where udd2.ValueKey like '%2' and
                    udd2.UserId = udd.UserId
             );

Upvotes: 2

Related Questions