Group by problem

I have a problem with Group by function (MySQL, Garafana V10)
I’d like to make monthly summary based on energy counter values (kWh). Data are stored every 15 minutes with timestamp and I know that there would be two solutions.

  1. option: max(counter) - min(counter) grouping monthly. But the problem is that the consumption between first value of the month and last value of the previous month is missing.
  2. option: to calculate countervalue difference between every record with LAG function and summarize it. This works but when I add Group by Month(Zeit) it results wrong figures.
    This is the result of consumption with Month(Zeit) function:
    image
    And the query:
    SELECT
    Month(zeit) as Time,
    Zaehlerstand-lag(Zaehlerstand) over (order by zeit) as “Consumption”
    FROM database.table
    where $__timeFilter(Zeit)
    order by zeit

But when I make grouping with Group by:
Group by Month(Zeit)
Month 12 is missing and all data belong to Month 12 is appear at Month 1
image

What is the problem, please help

What kind of aggregation do you want?
Sum. Average or ?

And which visualization

As I wrote, summarizing of delta counters (Consumptions).
Bar chart is needed.

1 Like

Subquery would be the solution but I have problem with this query:
(
SELECT
Year (zeit) as Year,
Month(zeit) as Month,
Zaehlerstand-lag(Zaehlerstand) over (order by zeit) as “Consumption”
FROM table
) as T
select * FROM T group by Year, Month

What could be the problem?

select * 
FROM (
SELECT
Year (zeit) as Year,
Month(zeit) as Month,
Zaehlerstand-lag(Zaehlerstand) over (order by zeit) as “Consumption”
FROM table
) T group by Year, Month

Thanks, but Consumtion needs to be summarized and at Month 12 value is missing

is zeit column UTC?

How can I check it?
You can be right, there could be a problem with the time because without subquery and grop by there is a time slip:


After 1 a.m. switch to Month 2

I would ask your dba or whomever set it up. We can’t do remote troubleshooting :wink:

Zeit is UTC, checked and setup in Grafana. There is no timeslip now.
How to summarize Consumption in Group by? SUM() doesn’t work.
And why the data at Month 12 is missing?

What does this say.

image