Joining multiple queries of SUM into one result

Question

So.... we have three different tables that pertains to a contest in which the DB keeps track of how many points they've received for each contest. Contest 1, 2 and 3. Each time that user achieves something, a new row is created for that user with the additional points. So in order to calculate all the points that user has received I use a select sum

SELECT userID, SUM(amount1) as "Contest 1 Points"
FROM [Company].[dbo].[Contest1]
WHERE userid not in (0,1)
GROUP BY userId
ORDER BY userid

because I have two more contests I do a query for each of those as well...

SELECT userId, SUM(amount2)/.65 AS "Category 2 Points"
FROM [Company].[dbo].[Contest2]
WHERE dateGiven >=201301 AND dateGiven <= 201305
GROUP BY userId
ORDER BY userid



SELECT userid, SUM(amount3) AS "Category 3 Points"
FROM [Company].[dbo].[Contest3]
where userid not in (1,2)
GROUP BY userid
ORDER BY userid

I basically need to add up all the points each user receives from each contest into 1 column that basically shows results USERID, TOTAL OF TOTALS(Contest1 + Contest2 + Contest3)

or at least have it like,

USER, Contest1 Total, Contest2 Total, Contest3 Total

The way I did this so far was copy/paste each of these results into excel and then I used VLOOKUP to match them up to eachother, which was kind of a hassle and im sure theres a way to do it in SQL. Im pretty new to SQL and I've tried joining and usig ON for matching the userid but there's something wrong with my syntax and how I understand that it all plugs into itself for queries.

Answer 1

You need to UNION the results:

SELECT userID, SUM(Points) AS total
FROM
 ( 
   SELECT userID, SUM(amount1) AS "Points"
   FROM [Company].[dbo].[Contest1]
   WHERE userid NOT IN (0,1)
   GROUP BY userId

   UNION ALL       

   SELECT userId, SUM(amount2)/.65 AS "Category 2 Points"
   FROM [Company].[dbo].[Contest2]
   WHERE dateGiven >=201301 AND dateGiven <= 201305
   GROUP BY userId

   UNION ALL       

   SELECT userid, SUM(amount3) AS "Category 3 Points"
   FROM [Company].[dbo].[Contest3]
   WHERE userid NOT IN (1,2)
   GROUP BY userid
 ) AS dt
GROUP BY userID
ORDER BY 2 DESC;

Edit: to get three seperate columns you simply use three SUMs instead of one:

SELECT userID, SUM("Category 1 Points"), SUM("Category 2 Points"), SUM("Category 3 Points") 
FROM
 ( 
   SELECT userID, SUM(amount1) AS "Category 1 Points"
   FROM [Company].[dbo].[Contest1]
   WHERE userid NOT IN (0,1)
   GROUP BY userId

   UNION ALL       

   SELECT userId, SUM(amount2)/.65 AS "Category 2 Points"
   FROM [Company].[dbo].[Contest2]
   WHERE dateGiven >=201301 AND dateGiven <= 201305
   GROUP BY userId

   UNION ALL       

   SELECT userid, SUM(amount3) AS "Category 3 Points"
   FROM [Company].[dbo].[Contest3]
   WHERE userid NOT IN (1,2)
   GROUP BY userid
 ) AS dt
GROUP BY userID
ORDER BY 2 DESC;

Of course there's only a single row per userDI/category so MIN or MAX would return the same result. This will return NULL for non-existing data, if you want 0 instead use COALESCE("Category x Points", 0).

You can also join the result sets, but unless it's guaranteed that each user particpated in each contest you need FULL OUTER JOINs using COALESCE:

SELECT userID, "Category 1 Points", "Category 2 Points", "Category 3 Points"
FROM
 ( 
   SELECT userID, SUM(amount1) AS "Category 1 Points"
   FROM [Company].[dbo].[Contest1]
   WHERE userid NOT IN (0,1)
   GROUP BY userId
 ) AS t1
FULL JOIN
ON t1.userID = t2.userID
 (
   SELECT userId, SUM(amount2)/.65 AS "Category 2 Points"
   FROM [Company].[dbo].[Contest2]
   WHERE dateGiven >=201301 AND dateGiven <= 201305
   GROUP BY userId
 ) AS t2
FULL JOIN
 (
   SELECT userid, SUM(amount3) AS "Category 3 Points"
   FROM [Company].[dbo].[Contest3]
   WHERE userid NOT IN (1,2)
   GROUP BY userid
 ) AS t3
ON COALESCE(t1.userID, t2.userID) = t3.userID
ORDER BY 2 DESC;