Grafana and MariaDB, strange behaviour in SQL query

Hello,
I am trying to move to Grafana and MariaDB within my openHAB configuration. In my post here


I explained how to get data from an SMA PV converter to a mysql database.

In Grafana I want to sum up the power of three phases using this sql query:

SELECT
  $__unixEpochGroupAlias(TimeStamp, $__interval),
  sum(Pac1 + Pac2 + Pac3) AS "Total Pac"
FROM SpotData
WHERE
  $__unixEpochFilter(TimeStamp, $__interval)
GROUP BY 1
ORDER BY $__unixEpochGroup(TimeStamp, $__interval)

This works ok for time ranges smaller than two days:

As you can see the three phases are summed up correctly to 8.7 kW.

If I now choose a time range of says for example 7 days I get:

As you can see the sum up is wrong, it should be 9kW but it is 27.1 kW

Any hints are very welcome.

This is more a Grafana query than something specific to openHAB. Have you asked them?

No, I have no Grafana forum account yet and I though maybe Grafana openHAB specialists might have a hint.

Which column is referenced by GROUP BY 1?

I don’t know were it comes from.
For the three phases I use the grafana wizard:

The “Total Pac” was a trial and error approach without knowing what I really do :roll_eyes:
However, removing it doesn’t work -> Values ar wrong even for small time ranges

No, the grouping is not optional, but I wonder if there are several identical values (and therefor grouped as one…)

FYI:
SBFspot ( https://github.com/SBFspot/SBFspot ) only puts the power of the three phases in the SQL history:


The total power is overwritten on each call to SPF spot in the sql database:

So I though I just sum Pac1, Pac2 and Pac3 to visualize the total power.