将多个SUM查询合并为一个结果

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

所以....我们有三个不同的表格是关于一个竞赛的，在这个表格中，DB会记录他们在每个比赛中获得了多少分。第1、2和3场比赛。每当用户实现某项功能时，就会为该用户创建带有附加点的新行。为了计算用户收到的所有点，我使用一个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)

我基本上需要把每个用户从每个比赛收到的所有分数加到1列，基本上显示结果USERID，总计(contest 1 + contest 2 + contest 3)

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.

到目前为止，我的方法是将这些结果复制/粘贴到excel中，然后使用VLOOKUP来匹配它们，这有点麻烦，我肯定有一种方法可以用SQL来完成。我对SQL非常陌生，我尝试过加入和usig来匹配userid，但我的语法有问题，我也不知道它是如何插入到查询中的。

1 个解决方案

#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).

当然，每个userDI/category只有一行，所以MIN或MAX会返回相同的结果。如果您想要0而不是使用COALESCE(“类别x点”，0)，那么对于不存在的数据，这将返回NULL。

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:

您也可以加入结果集，但是除非它保证每个用户都参与了每个竞赛，否则您需要使用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;

#1