CODEWITHSUNDEEP
javamysqlselectinsertdistinct
lina20
lina20

Reputation: 25

Mysql insert into with select distinct

i'm using java with mysql,i have this code in mysql

SELECT DISTINCT date(datecreat) as datetemp , (SELECT max(sensortemp) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) as maxt , (SELECT min(sensortemp) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) as mint , (SELECT avg(sensortemp) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) as avgt , (SELECT max(sensorhum) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) as maxh , (SELECT min(sensorhum) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) as minh , (SELECT avg(sensorhum) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) as avgh , (SELECT max(sensorpres) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) as maxp , (SELECT min(sensorpres) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) as minp , (SELECT avg(sensorpres) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) as avgp FROM akdb.iotdatas where (serialnum ='0000000000000');

the result is like this:

enter image description here but i want to insert this data into other table using this code

insert into akdb.climdatas(hightemp,lowtemp,avgtemp,highhum,lowhum,avghum,highpress,lowpress,avgpress,dateinsert,zonedatas)values(maxt,mint,avgt,maxh,minh,avgh,maxp,minp,avgp,datetemp,'zone h');

here the problem is "maxt mint ..." are unknown, but if i use select into insert

insert into akdb.climdatas(SELECT DISTINCT date(datecreat) as datetemp ,(SELECT max(sensortemp) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) , (SELECT min(sensortemp) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) , (SELECT avg(sensortemp) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) , (SELECT max(sensorhum) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) , (SELECT min(sensorhum) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) , (SELECT avg(sensorhum) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) , (SELECT max(sensorpres) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) , (SELECT min(sensorpres) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) , (SELECT avg(sensorpres) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ),' zone h'FROM akdb.iotdatas where (serialnum ='0000000000000000'))

the error is in the columns "mysql column count doesn't match value count at row 1"

Upvotes: 0

Views: 811

Answers (1)

Keyur Panchal
Keyur Panchal

Reputation: 1402

Seems like you have more or less than 11 columns in climdatas table. In your insert statement, specify list of columns separated by comma.

insert into akdb.climdatas (dateinsert,hightemp,lowtemp,avgtemp,highhum,lowhum,avghum,highpress,lowpress,avgpress,zonedatas)
(your select query goes here.)

Upvotes: 1

Related Questions