Inserting one MySQL row with values from multiple, nested SELECT statements

Question

I'm trying to combine the following two statements into one SELECT statement, so that I can insert the returned values into one and the same row with one statement.

SELECT ROUND(AVG(pressure),1) FROM rawinput WHERE timestamp >= SUBDATE(timestamp(now()), INTERVAL 1 HOUR);

SELECT ROUND((SELECT AVG(hourly_average_pressure) FROM hour_numbers WHERE timestamp >= CURTIME() - INTERVAL 1 HOUR)-(SELECT AVG(hourly_average_pressure) FROM hour_numbers WHERE timestamp >= CURTIME() - INTERVAL 2 HOUR AND timestamp < CURTIME() - INTERVAL 1 HOUR),1);

I already tried to format like below (basically adding parentheses around both SELECT statements and replace the semicolon with a comma) but I got syntax error. Is the only way to do this using a UNION ALL or am I missing something in the format? Thanks for the help.

(SELECT ROUND(AVG(pressure),1) FROM rawinput WHERE timestamp >=SUBDATE(timestamp(now()), INTERVAL 1 HOUR)), (SELECT ROUND((SELECT AVG(hourly_average_pressure) FROM hour_numbers WHERE timestamp >= CURTIME() - INTERVAL 1 HOUR)-(SELECT AVG(hourly_average_pressure) FROM hour_numbers WHERE timestamp >= CURTIME() - INTERVAL 2 HOUR AND timestamp < CURTIME() - INTERVAL 1 HOUR),1));

Could you please clarify what your expected output is and how those two statements are connected (they have the same timestamps, ids whatever)? — Kosh
– Kosh, Commented Feb 23, 2018 at 17:16
Sure, I was trying to insert the returned values from both of these select statements into one row (one insert statement). None of the fields are allowed to be null so, when inserting data with a timestamp about calculated values for temperature and pressure, it has to be done in one statement, so they will be timestamped with the same timestamp... (The database is used to collect data from Raspberry Pi weather monitoring station) — bimmo
– bimmo, Commented Feb 23, 2018 at 18:58

Eric · Accepted Answer · 2018-02-23 17:22:31Z

1

If you format your code properly, it's easy to see that you are missing SELECT statement in front of your code.

(
    SELECT ROUND(AVG(pressure),1) 
    FROM rawinput 
    WHERE timestamp >=SUBDATE(timestamp(now()), INTERVAL 1 HOUR)
)
, (
    SELECT ROUND((SELECT AVG(hourly_average_pressure) FROM hour_numbers WHERE timestamp >= CURTIME() - INTERVAL 1 HOUR)-(SELECT AVG(hourly_average_pressure) 
    FROM hour_numbers 
    WHERE timestamp >= CURTIME() - INTERVAL 2 HOUR AND timestamp < CURTIME() - INTERVAL 1 HOUR),1)
);

With SELECT

SELECT (
    SELECT ROUND(AVG(pressure),1) 
    FROM rawinput 
    WHERE timestamp >=SUBDATE(timestamp(now()), INTERVAL 1 HOUR)
)
, (
    SELECT ROUND((
        SELECT AVG(hourly_average_pressure) 
        FROM hour_numbers 
        WHERE timestamp >= CURTIME() - INTERVAL 1 HOUR
    ) - (
        SELECT AVG(hourly_average_pressure) 
        FROM hour_numbers 
        WHERE timestamp >= CURTIME() - INTERVAL 2 HOUR AND timestamp < CURTIME() - INTERVAL 1 HOUR
    ), 1)
);

answered Feb 23, 2018 at 17:22

Eric

3,2701 gold badge22 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bimmo Over a year ago

Thanks Eric, that worked perfectly. Proper formatting like you demonstrated will undoubtedly prevent many more of my problems with various stored procedures I'm putting together, thanks :-)

Collectives™ on Stack Overflow

Inserting one MySQL row with values from multiple, nested SELECT statements

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related