Get row Sums across multiple users/tables

user_323545 0
Viewed 37

I am working in SQL Server, and I have three tables that I have simplified below

USERS:

UserId  | FirstName | LastName  | Email
--------+-----------+-----------+------------------
1       |    Joe    | Johnson   | jjohnson@work.com
2       |   Mary    | Smith     | msmith@work.com

POINTS:

UserId  | PointsEarned
--------+--------------
1       |   25
2       |   50
1       |   50
1       |   10
2       |   20

CLAIMS:

UserId | PointsUsed
-------+-----------
1       |   50
2       |   20
1       |   10
1       |   10
2       |   5

I'm trying to run a report of distinct users with a sum of points earned and a sum of points used. I've started with the join of tables by userId...

SELECT  
    Users.UserId, Users.FirstName, Users.LastName, Users.Email, 
    Points.PointsEarned, Claims.PointsUsed
FROM
    Users 
INNER JOIN
    Points ON Points.UserId = Users.UserId 
INNER JOIN
    Claims ON Users.UserId = Claims.UserId

but of course that returns the user multiple times, I've tried many variations of Group By, Distinct, etc but can't seem to get there. Users are (in theory) already distinct by Id or email, the entries into the other two tables are dozens up to hundreds per user.

Desired output would be something like this

UserId  | FirstName | LastName  | Email             | PointsEarned  | PointsUsed
1       |    Joe    | Johnson   | jjohnson@work.com |   85          |   70
2       |   Mary    | Smith     | msmith@work.com   |   70          |   25

Any help is appreciated.

sql-server sql
2 Answers

Try it with subqueries and LEFT JOINS in case there are Users without points.

select u.userid, u.firstname, u.lastname, u.email, p.pointsearned, c.pointsused
from [users] u
left join (Select userid,sum(pointsearned) as pointsearned from points group by userid) p on u.userid=p.userid
left join (Select userid,sum(pointsused) as pointsused from claims group by userid) c on u.userid=c.userid
order by u.userid, u.firstname, u.lastname, u.email
user_108640930

SQL Server supports APPLY which is another approach to the problem:

SELECT  u.*, p.PointsEarned, c.PointsUsed
FROM Users u OUTER APPLY
     (SELECT SUM(p.PointsEarned) as PointsEarned
      FROM Points p
      WHERE p.UserId = u.UserId
     ) p OUTER APPLY
     (SELECT SUM(c.PointsUsed) as PointsUsed
      FROM Claims c
      WHERE u.UserId = c.UserId
     ) c;

This uses OUTER APPLY, so you get all users, even those with no points. Use CROSS APPLY if you want only users that have both claims and points.

The advantage to this approach is performance. It probably works better than two aggregations with joins (assuming there is an index on UserId in the tables). It definitely works better if you are filtering the users in the outer query.

user_11440350
Related