SQL sensor for reading energy data

Configuration
1

Can some SQL guru please help me with a SQL sensor syntax?

The first code works nicely

- platform: sql
  db_url: !secret db_url
  queries:
    - name: DB size
      query: 'SELECT table_schema "database", Round(Sum(data_length + index_length) / 1024, 1) "value" FROM information_schema.tables WHERE table_schema="homeassistant" GROUP BY table_schema;'
      column: "value"
      unit_of_measurement: kB

The following line executes nicely within phpMyAdmin …

SELECT `sum` FROM `statistics` WHERE `metadata_id`= 103 ORDER BY `id` DESC LIMIT 1

Question now, how do I put the above into a sensor? All of my attempts failed.

- platform: sql
  db_url: !secret db_url
  queries:
    - name: Dehumid grand total
      query: '<some magic here'
      column: "sum"
      unit_of_measurement: kWh

My db_url reads db_url: mysql://homeassistant:xxx@core-mariadb/homeassistant?charset=utf8mb4

2

Apparently, I’m a guru myself :wink:

Here is the solution

- platform: sql
  db_url: !secret db_url
  queries:
    - name: Dehumid grand total
      query: "SELECT * FROM statistics WHERE metadata_id=103 ORDER BY id DESC LIMIT 1;"
      column: "sum"
      unit_of_measurement: kWh
1 Like