Sum Values Between Two Tables

Question

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?

score 2 · Accepted Answer · 2014-04-03T19:06:01.400

2

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

edited Apr 03 '14 at 19:06

answered Apr 03 '14 at 18:44

Now test without the group by as my answer... what do you know... shocking! that works too. – Hogan Apr 03 '14 at 18:50
@Hogan I updated my answer. Yes it does work as long as there is only 1 row per ID. – Apr 03 '14 at 19:06

score 1 · Answer 2 · answered Apr 03 '14 at 18:47

1

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

answered Apr 03 '14 at 18:47

Hogan

69,564
10
76
117

score 0 · Answer 3 · answered Apr 03 '14 at 18:48

0

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

answered Apr 03 '14 at 18:48

timo.rieber

3,727
3
32
47

This is horribly slow compared to doing two joins. – Hogan Apr 03 '14 at 18:51
I don't think so because there is no need for a lookup. It simply returns all rows as your query would also as there is no value without a related user. You're query even has to read one more table AND do the joins. – timo.rieber Apr 03 '14 at 18:55
you would not think so if you didn't know (as I do) that sql engines are much faster at doing joins than they are at doing UNION ALLs and GROUP BYs. But feel free to test it out... or work with SQL for 25 years as I have. – Hogan Apr 03 '14 at 18:58
What about the execution plans? Have you tried this concrete use case right now (not in your long history)? Here are some other opinions voting for a UNION ALL: http://stackoverflow.com/questions/3374459/can-union-all-be-faster-than-joins-or-do-my-joins-just-suck – timo.rieber Apr 03 '14 at 19:16
Did you read the page you linked -- union all is only faster when when you don't need the group by that is as they say when the two set don't intersect -- but please don't trust me... test it yourself. Then you can thank me. – Hogan Apr 03 '14 at 19:28

Sum Values Between Two Tables

3 Answers3