mysql union get back Error 1022 — How can i fixed this?

Question

my weather schemes have two different tables rainfall and outside temp.

i try this :

SELECT 
 savetime
 ,UNIX_TIMESTAMP(savetime) as unixt
 ,(MAX(counteramount) - MIN(counteramount))*295/1000 as rainc 
 from rainfall 
 union 
 Select 
 savetime
 ,avg(weatherdata)as temp 
 FROM outside_temp 
 where savetime >= DATE_SUB(CURDATE(), INTERVAL 90 DAY) 
 group by day(savetime)

but get back failure: #1222 - The SELECT commands used return different numbers of fields

I have no Plan how to fix this...

Best regards Quito

Answer 1

UNION has to have teh same number of columns, so add simpo,y alos a unixtome column and you are good

SELECT 
    savetime,
    UNIX_TIMESTAMP(savetime) AS unixt,
    (MAX(counteramount) - MIN(counteramount)) * 295 / 1000 AS rainc
    ,''
FROM
    rainfall 
UNION SELECT 
    savetime,
    UNIX_TIMESTAMP(savetime) AS unixt,
    ,''
    AVG(weatherdata) AS temp
FROM
    outside_temp
WHERE
    savetime >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
GROUP BY DAY(savetime)

CREATE TABLE outside_temp (
  `savetime` DATETIME,
  `weatherdata` FLOAT,
  `newflag` INTEGER
);

INSERT INTO outside_temp
  (`savetime`, `weatherdata`, `newflag`)
VALUES
  ('2020-01-31 01:10:00', '8.9', '1'),
  ('2020-01-31 03:25:00', '8.9', '1'),
  ('2020-01-31 05:40:00', '9.1', '1'),
  ('2020-01-31 00:10:00', '8.8', '1'),
  ('2020-01-31 04:25:00', '9.3', '1'),
  ('2020-01-31 00:55:00', '9', '1'),
  ('2020-01-31 01:25:00', '8.9', '1'),
  ('2020-01-31 00:25:00', '8.9', '1'),
  ('2020-01-31 04:10:00', '9.3', '1');

CREATE TABLE rainfall (
  `savetime` VARCHAR(34),
  `raincounter` INTEGER,
  `counteramount` INTEGER,
  `israining` INTEGER,
  `newflag` INTEGER
);

INSERT INTO rainfall
  (`savetime`, `raincounter`, `counteramount`, `israining`, `newflag`)
VALUES
  ('2020-01-31 09:10:00', '2016', '37290', '0', '1'),
  ('2020-01-31 16:56:00', '2016', '37290', '0', '1'),
  ('2020-01-31 07:40:00', '2016', '37290', '0', '1'),
  ('2020-01-31 05:10:00', '2016', '37290', '0', '1'),
  ('2020-01-31 11:11:00', '2016', '37290', '0', '1'),
  ('2020-01-31 15:11:00', '2016', '37290', '0', '1'),
  ('2020-01-31 20:56:00', '2016', '37290', '0', '1'),
  ('2020-01-31 11:41:00', '2016', '37290', '0', '1'),
  ('2020-01-31 08:55:00', '2016', '37290', '0', '1'),
  ('2020-01-31 15:41:00', '2016', '37290', '0', '1'),
  ('2020-01-31 12:11:00', '2016', '37290', '0', '1');

SELECT 
    savetime_temp
    ,AVG_temp
    ,rainc
FROM
(SELECT 
    DATE(savetime) DAY_temp
,MIN(savetime) savetime_temp
    ,AVG(weatherdata) AS AVG_temp
FROM
    outside_temp
WHERE
    savetime >= DATE_SUB(CURDATE(), INTERVAL 360 DAY)
GROUP BY  DAte(savetime)) t1 
INNER JOIN
(SELECT 
    DATE(savetime) DAY_rainc
    ,MIN(savetime) savetime_rainc
    ,(MAX(counteramount) - MIN(counteramount)) * 295 / 1000 AS rainc

FROM
    rainfall 
 GROUP BY DAte(savetime)) t2
 ON t1.DAY_temp = t2.DAY_rainc

savetime_temp       |          AVG_temp |  rainc
:------------------ | ----------------: | -----:
2020-01-31 00:10:00 | 9.011111047532824 | 0.0000

db<>fiddle here