Solved it. First I had a problem that the sensor was updating to unknown. This was due to the fact an sensor state can only have max. 255 characters.
I solved it with a HACS integration called sql_json.
And than in de configuration yalm:
sensor:
- platform: sql_json
scan_interval: 86400
db_url: mysql://user:pass@core-mariadb/homeassistant?charset=utf8mb4
queries:
- name: "Vogelranglijst"
query: >-
SELECT CONCAT('[', GROUP_CONCAT(vogel_json), ']') AS json
FROM (
WITH states AS (
SELECT state, COUNT(*) AS vogel_count
FROM (
SELECT state FROM states
INNER JOIN states_meta ON states.metadata_id = states_meta.metadata_id
WHERE states_meta.entity_id = 'sensor.garden_birds'
AND last_updated_ts >= UNIX_TIMESTAMP(CURDATE())
) AS states
GROUP BY state
ORDER BY vogel_count DESC
)
SELECT JSON_OBJECT(
'state', state,
'count', vogel_count
) AS vogel_json
FROM states
/*LIMIT 10*/
) AS json_output;
value_template: '{{ value_json[0].count }}'
unit_of_measurement: vogels
column: json
After that I used the example in de documentation to form a front-end flex table card:
- type: custom:flex-table-card
columns:
- data: json
modify: x.state
name: Vogels
- data: json
modify: x.count
name: vogels/dag
entities:
include: sensor.vogelranglijst
The end result:
