CODEWITHSUNDEEP
mysql-workbench
Tia
Tia

Reputation: 119

Average MySQL in new table

I have a database about weather that updates every second. It contains temperature and wind speed. This is my database:

CREATE TABLE `new_table`.`test` (
`id` INT(10) NOT NULL,
`date` DATETIME() NOT NULL,
`temperature` VARCHAR(25) NOT NULL,
`wind_speed` INT(10) NOT NULL,
`humidity` FLOAT NOT NULL,
 PRIMARY KEY (`id`))
 ENGINE = InnoDB
 DEFAULT CHARACTER SET = utf8
 COLLATE = utf8_bin;

I need to find the average temperature every hour. This is my code:

Select SELECT AVG( temperature ), date
FROM new_table
GROUP BY HOUR ( date )

My coding is working but the problem is that I want to move the value and date of the average to another table. This is the table: 

CREATE TABLE `new_table.`table1` (
`idsea_state` INT(10) NOT NULL,
`dateavg` DATETIME() NOT NULL,
`avg_temperature` VARCHAR(25) NOT NULL,
 PRIMARY KEY (`idsea_state`))
 ENGINE = InnoDB
 DEFAULT CHARACTER SET = utf8
 COLLATE = utf8_bin;

Is it possible? Can you give me the coding?

Upvotes: 0

Views: 508

Answers (1)

the_velour_fog
the_velour_fog

Reputation: 2184

In order to insert new rows into a database based on data you have obtained from another table, you can do this by setting up an INSERT query targeting the destination table, then run a sub-query which will pull the data from the source table and then the result set returned from the sub-query will be used to provide the VALUES used for the INSERT command

Here is the basic structure, note that the VALUES keyword is not used:

INSERT INTO `table1`
(`dateavg`, `avg_temperature`) 
  SELECT `date` , avg(`temperature`)
  FROM   `test`;

Its also important to note that the position of the columns returned by result set will be sequentially matched to its respective position in the INSERT fields of the outer query

e.g. if you had a query 

INSERT INTO table1 (`foo`, `bar`, `baz`)
    SELECT (`a`, `y`, `g`) FROM table2
  • a would be inserted into foo
  • y would go into bar
  • g would go into baz

due to their respective positions

I have made a working demo - http://www.sqlfiddle.com/#!9/ff740/4 I made the below changes to simplify the example and just demonstrate the concept involved.
Here is the DDL changes I made to your original code 

CREATE TABLE `test` (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `date` DATETIME NOT NULL,
  `temperature` FLOAT NOT NULL,
  `wind_speed` INT(10),
  `humidity` FLOAT ,
 PRIMARY KEY (`id`))
 ENGINE = InnoDB
 DEFAULT CHARACTER SET = utf8
 COLLATE = utf8_bin;

 CREATE TABLE `table1` (
`idsea_state` INT(10) NOT NULL AUTO_INCREMENT,
`dateavg` VARCHAR(55),
`avg_temperature` VARCHAR(25),
 PRIMARY KEY (`idsea_state`))
 ENGINE = InnoDB
 DEFAULT CHARACTER SET = utf8
 COLLATE = utf8_bin;

INSERT INTO `test` 
(`date`,  `temperature`) VALUES
('2013-05-03', 7.5),
('2013-06-12', 17.5),
('2013-10-12', 37.5);

INSERT INTO `table1`
(`dateavg`, `avg_temperature`) 
  SELECT `date` , avg(`temperature`)
  FROM   `test`;

Upvotes: 1

Related Questions