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-03 19:06:01Z

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 3, 2014 at 19:06

answered Apr 3, 2014 at 18:44

user275683

Sign up to request clarification or add additional context in comments.

2 Comments

Hogan Over a year ago

Now test without the group by as my answer... what do you know... shocking! that works too.

user275683 Over a year ago

@Hogan I updated my answer. Yes it does work as long as there is only 1 row per ID.

Hogan · Accepted Answer · 2014-04-03 18:47:50Z

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 3, 2014 at 18:47

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

Comments

timo.rieber · Accepted Answer · 2014-04-03 18:48:50Z

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 3, 2014 at 18:48

timo.rieber

3,8773 gold badges34 silver badges48 bronze badges

5 Comments

Hogan Over a year ago

This is horribly slow compared to doing two joins.

timo.rieber Over a year ago

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.

Hogan Over a year ago

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.

timo.rieber Over a year ago

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: stackoverflow.com/questions/3374459/…

Hogan Over a year ago

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.

Collectives™ on Stack Overflow

Sum Values Between Two Tables

3 Answers 3

2 Comments

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related