CODEWITHSUNDEEP
sqlsql-server-2008-r2
Sven Grosen
Sven Grosen

Reputation: 5636

Sum Values Between Two Tables

I have the following tables:

create table Users (
  Id int
)

create table Vals1 (
  UserId int,
  Number int
)

create table Vals2 (
  UserId int,
  Number int
)

I have values in Vals1 and Vals2 but not a value for every UserId. What I want to do is, when available, sum the corresponding values between Vals1 and Vals2.

In this example, assume I have records with Id 1-5 in my User table. So, say I have the following in Vals1:

UserId     Number
1          10
2          15
4          20

And this in Vals2:

UserId     Number
1          30
2          55
3          40

This is what I want as output:

UserId       Number
1            40
2            70
3            40
4            20
5            0

My first stab at this produces correct results, but this seems really ugly:

;WITH AllVals1 AS (
  SELECT Id, ISNULL(Number, 0) as Number
  FROM Users
  LEFT JOIN Vals1 ON Id = UserId
), AllVals2 AS (
  SELECT Id, ISNULL(Number, 0) as Number
  FROM Users
  LEFT JOIN Vals2 ON Id = UserId
)
SELECT v1.Id, v1.Number + v2.Number
FROM AllVals1 v1
JOIN AllVals2 v2 ON v1.Id = v2.Id

Is there a more succinct/efficient way of doing this?

Upvotes: 0

Views: 51

Answers (3)

user275683
user275683

Reputation:

Here is much simpler way of doing it, this will only work if you have 1 row per ID in Vals1 and Vals2 table. 

SELECT id, COALESCE(v1.Number, 0) + COALESCE(v2.Number, 0) as NumberSum
    FROM users u
    LEFT OUTER JOIN Vals1 AS v1
        ON u.id = v1.userid
    LEFT OUTER JOIN vals2 AS v2
        ON u.id = v2.userid

If you have more than 1 row per ID in values table than you can add SUM() and GROUP BY clause to get rid of multiple rows.

SELECT id
       ,SUM(COALESCE(v1.Number, 0) + COALESCE(v2.Number, 0))
    FROM users u
    LEFT OUTER JOIN Vals1 AS v1
        ON u.id = v1.userid
    LEFT OUTER JOIN vals2 AS v2
        ON u.id = v2.userid
    GROUP BY id

Upvotes: 2

timo.rieber
timo.rieber

Reputation: 3867

I suggest to first select all values in a derived temporary table and then calculate the sum grouping by user:

SELECT
    tmp.UserId,
    sum(tmp.Number) as Number
FROM
    (SELECT
        UserId,
        Number
    FROM
        Vals1

    UNION ALL

    SELECT
        UserId,
        Number
    FROM
        Vals2) tmp
GROUP BY
    tmp.UserId

Upvotes: 0

Hogan
Hogan

Reputation: 70528

You can use a left join and then account for the nulls with coalesce or isnull

SELECT users.id, ISNULL(vals1.number,0) + ISNULL(vals2.number,0) as [sum]
FROM users
left join vals1 on vals1.userid = users.id
left join vals2 on vals2.userid = users.id

Upvotes: 1

Related Questions