SQL Sensor not working

Configuration
1

I guess there are some smart people here who can help me out.
I am trying to create a sql sensor. First approach was the following in the configuration.yaml:

  # Sensors for energy consumption per hour
  - platform: sql
    scan_interval: 600
    #db_url: !secret db_url
    queries:
      - name: Electricity usage per hour
        unique_id: electricity_usage_per_hour
        column: "hourly_averages"
        unit_of_measurement: kWh
        query: |
          SELECT AVG(state) as total_average, GROUP_CONCAT(state ORDER BY hour) as hourly_averages
             FROM (SELECT ROUND(AVG(state), 0) as state, strftime("%H",datetime(last_updated_ts,"unixepoch")) as hour
                 FROM states INNER JOIN states_meta ON states_meta.metadata_id=states.metadata_id
                 WHERE states_meta.entity_id = 'sensor.p1_meter_5c2faf102094_active_power'
                 GROUP BY strftime("%H",datetime(last_updated_ts,"unixepoch")) 
                 );

However no sensor dit popup after a restart and loading the config.
Then I did read something that in the new format the following configuration should be used:

sql:
  - name: Electricity usage per hour
    unique_id: electricity_usage_per_hour
    column: "hourly_averages"
    unit_of_measurement: kWh
    query: >
      SELECT AVG(state) as total_average, GROUP_CONCAT(state ORDER BY hour) as hourly_averages
         FROM (SELECT ROUND(AVG(state), 0) as state, strftime("%H",datetime(last_updated_ts,"unixepoch")) as hour
             FROM states INNER JOIN states_meta ON states_meta.metadata_id=states.metadata_id
             WHERE states_meta.entity_id = 'sensor.p1_meter_5c2faf102094_active_power'
             GROUP BY strftime("%H",datetime(last_updated_ts,"unixepoch")) 
             );

However that also did not show any sensor.
When I run the SQL query in the SQLite interface it gives me the required output. So I know the query is correct.
What am I missing here?

2

You must set up SQL sensors from the UI. The YAML option has been deprecated and removed (your log should’ve told you it’s an invalid config option).

You will also need to use an automation to update the sensor on a custom interval. There’s no scan interval option anymore. I cannot remember what the default for this integration is.

3

Thanks for the response. That actually did the trick indeed.

1 Like