Join two Mysql tables by unique keywords and by adding total in column

Question

I am trying to get daily totals for each location into another table.

Here is the structure:

reports_location_temp:

Table_Name:           Date:                              Total_Count:
London                2013-05-26 10:49:53                5000
London                2013-05-26 10:49:53                2000
Birmingham            2013-05-26 10:49:53                1000
London                2013-05-26 10:49:53                5000
Manchester            2013-05-26 10:49:53                50
Birmingham            2013-05-26 10:49:53                500

reports_location_total_daily:

Table_Name:           Date:                              Total_Count:
London                2013-05-26 23:55:00                12000
Manchester            2013-05-26 23:55:00                50
Birmingham            2013-05-26 23:55:00                1500

I am still learning my way around Mysql.

This is the query i tried but it only picked out one column for each Unique Table_Name:

UPDATE reports_Location_total_daily j1 INNER JOIN reports_location_temp l1 ON j1.Table_Name = l1.Table_Name SET j1.Total_Count = l1.Total_Count

Thanks for your help with this.

Why not write 2 requests: select from the 1-st, group by `Table_Name` and UPDATE the 2-nd table? — user4035, May 26 '13 at 12:06
Investigate the command `insert into . . . on duplicate key`. — Gordon Linoff, May 26 '13 at 13:22

score 0 · Accepted Answer · answered May 26 '13 at 12:16

CREATE TABLE sales
(id INT NOT NULL AUTO_INCREMENT,
location VARCHAR(40), 
today DATETIME NOT NULL,
sales INT NOT NULL,
PRIMARY KEY (id)
)
;


INSERT sales (location,today,sales) VALUES ('London','2013-05-26',2000);
INSERT sales (location,today,sales) VALUES ('Birm','2013-05-26',1000);
INSERT sales (location,today,sales) VALUES ('London','2013-05-26',1500);
INSERT sales (location,today,sales) VALUES ('London','2013-05-24',100);
INSERT sales (location,today,sales) VALUES ('Birm','2013-05-24',200);
INSERT sales (location,today,sales) VALUES ('London','2013-05-24',300);

CREATE TABLE daily_totals
(id INT NOT NULL AUTO_INCREMENT,
location VARCHAR(40), 
today DATETIME NOT NULL,
totalsales INT NOT NULL,
PRIMARY KEY (id)
)
;

DELETE FROM daily_totals;

INSERT INTO daily_totals (location,today,totalsales)
SELECT location,
DATE(today),
SUM(sales)
FROM sales
GROUP BY location,DATE(today)

Join two Mysql tables by unique keywords and by adding total in column

1 Answers1