从多个表中进行选择，并将1个表中的所有匹配项相加

Question

I have two tables, one with an accountID, hours, and date columns and the other with accountID, minutes, and date columns. The first table will only ever have 1 entry for each date but the second could have multiple. What I want to do is select everything from both tables where the accountID's match up, but add up the minutes of each occurrence in the second table.

I apologize if this makes no sense. Here's an example:

Table1:

Account ID | hours | date
1             4       2014-1-1
1             3       2014-1-2
2             2       2014-1-3
3             3       2014-1-3

Table2:

Account ID | minutes | date
1            34        2014-1-1
1            36        2014-1-1
1            32        2014-1-2
1            12        2014-1-2

I don't want more than 1 row per day so the minutes for Jan 1 and Jan 2 need to be combined, is this possible?

EDIT: I want the hours from table 1 and the summation of the minutes by date from table 2. So on Jan 1 minutes would be 70 and hours would be 4.

Answer 1

What you want is a group by clause on a table join. In this case you'll need a double group by since you want id, date pairings. You'll have to sum the hours even though you indicated they are unique to account ids to prevent the grouping clause from complaining.

SELECT `Account ID`, `date`, SUM(`hours`) AS `hours`, SUM(`minutes`) AS `minutes`
FROM `table1` 
JOIN `table2` ON (`table1`.`Account ID` = `table2`.`Account ID` AND `table1`.`date` = `table2`.`date`)
GROUP BY `Account ID`, `date`;