CODEWITHSUNDEEP
mysql
bimmo
bimmo

Reputation: 9

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

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));

Upvotes: 0

Views: 53

Answers (1)

Eric
Eric

Reputation: 3257

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)
);

Upvotes: 1

Related Questions