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?