从不同的表中对同一查询中的两列求和

I have a 2015 and 2016 tables which have the same columns. I'm trying to display the totals from the separate years of one column.

我有2015年和2016年的表，它们具有相同的列。我试图显示一列不同年份的总数。

SELECT t16.`Dep Name`, sum(t16.`number_courses`) AS `Total 16`, sum(t15.`number_courses`) 
AS `Total 15` FROM `table_16` t16, `table_15` t15
GROUP BY t16.`Dep Name`

This gives me wrong totals but when I do the totals separately, they work just fine. What am I doing wrong here?

这给了我错误的总数，但是当我单独进行总计时，它们工作正常。我在这做错了什么？

EDIT: Okay, my main goal was to create a view but apparently according to MySQL Documentation subqueries aren't supported by views. Is there another way I can achieve the same result and create a view?

编辑：好的，我的主要目标是创建一个视图，但显然根据MySQL文档子视图不支持视图。有没有其他方法可以实现相同的结果并创建一个视图？

1 个解决方案

#1

You are essentially doing a CROSS JOIN which produces a cartesian product of the rows of both tables.

你实际上是在做一个CROSS JOIN，它产生两个表行的笛卡尔积。

You can instead first aggregate and then join:

您可以先聚合然后加入：

SELECT t16.`Dep Name`, `Total 16`, `Total 15`
FROM (
  SELECT `Dep Name`, 
         sum(`number_courses`) AS `Total 16`
  FROM `table_16` 
  GROUP BY `Dep Name`) t16
JOIN (
  SELECT `Dep Name`, 
         sum(`number_courses`) AS `Total 15`
  FROM `table_15` 
  GROUP BY `Dep Name`
) t15 ON t16.`Dep Name` = t15.`Dep Name`

#1