ybce ybce - 1 year ago 56
SQL Question

Summing two columns in the same query from different tables

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.

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?

Answer Source

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

You can instead first aggregate and then join:

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