SQL code error (OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY)

Question

I'm trying to determine an average temperature over one day, for home assistant. I have linked sensors to home assistant, which send data to phpmyadmin. I can't send the sensors separately via home assistant, but they are all listed in the folder status. See below.

sensors in folder states

By sending a sql code via home assistant, I can search values in the database. So now I'm trying to find the average of the last 100 measurements (+/- 1 day). To achieve this, I have written the following sql code.

SELECT AVG(`state`) 
FROM states 
WHERE state_id > (select state_id 
                    FROM states 
                    WHERE entity_id='sensor.weatherstation_ground_temperature' 
                    AND state <> unknown 
                    ORDER BY state_id DESC 
                    OFFSET 0 ROWS FETCH FIRST 100 ROWS ONLY)

If I type this into phpmyadmin, I get the following error:

1064 - There is an error in the syntax used for 'OFFSET 0 ROWS FETCH FIRST 100 ROWS ONLY) LIMIT 0, 25' in line 1.

My question is: what am I doing wrong, or is there a better way?

Frech first rows only is not mysql syntax. Use limit instead. — Jens
– Jens, Commented Nov 15, 2019 at 12:36

GMB · Accepted Answer · 2019-11-15 14:22:11Z

0

fetch first does not exist in MySQL; you need limit instead.

SELECT AVG(s.`state`) 
FROM states s
INNER JOIN (
    SELECT state_id 
    FROM states 
    WHERE entity_id='sensor.weatherstation_ground_temperature'  AND state <> 'unknown' 
    ORDER BY state_id DESC 
    LIMIT 100
) x ON x.state_id = s.state_id

Other issues with your query:

subqueries that use LIMIT cannot be used with the IN operator: you can use a JOIN instead
state <> unknown should be spelled state <> 'unknown' (this is a syntax error)

Side note: OFFSET 0 is the default, so I can be ignored here.

edited Nov 15, 2019 at 14:22

answered Nov 15, 2019 at 12:59

GMB

224k25 gold badges103 silver badges151 bronze badges

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

4 Comments

Tibert Over a year ago

Sorry sql is all pretty new to me. When I enter the code I still get an error: #1054 - Unknown column 's.state' in field list I thought this might be the folder it was stored in and the column (sensor_logging.state). What should I change in this code?

GMB Over a year ago

@Tibert: sorry there was a typo, I just fixed it, can you please give it another try?

Tibert Over a year ago

The code works, but the output is NULL and the sensor does register numbers.

Tibert Over a year ago

I'm sorry my mistake had spelled something in dutch. Thank you so much!! this took me literly forever.

Collectives™ on Stack Overflow

SQL code error (OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY)

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related