[solved] Avoiding sumation of values -> use AVG and no grouping!

Get Help
1

Hi,

I have a lot of measurement-data from the past 40 years (water-levels of different rivers).
Sadly, the people who use and collected this data have some inconsistent data:
Because of rounding-errors and incomplete import-scripts there a a lot of data for same date and time, but slightly different measurement-values. I have no possibility to update or delete rows.
If I plot a graph via metabase, the error-triangle "unaggregated field" shows up in the question:
For the same date and time, the values are summarized. If I hover over the data-point, a single value is shown (not the sum).

We need the graph without summarized the values of same date+time.
My first idea was, to select the average-value of each single time-step of the data. So, if same-timesteps exists, the graph doesn't show the sum, but the average-value.
Does somebody know, how you can calculate and show this average for every timestep in a graph in metabase, so the peaks are gone?

exampleOfPeaks
exampleOfPeaks735×238 5.13 KB

I tried this:

SELECT gwpflumw_datetime, AVG(gwpflumw_deviceValue), gwpflumw_deviceID
FROM gwpflumw, gwpflumst
WHERE {{category}} AND {{datum}}
GROUP BY gwpflumw_datetime, gwpflumw_deviceValue, gwpflumw_deviceID

Background-info: example of the data:

gwpflumw_deviceID      gwpflumw_deviceValue         gy_datetime
8191                         112,56             2020-07-29 17:00:00
8191                         112,40             2020-07-29 17:05:00
8191                         112,52             2020-07-29 17:10:00 
8191                         112,50             2020-07-29 17:15:00 
8191                         112,60             2020-07-29 17:15:00

Should be interpreted like (change at the last two rows):

gwpflumw_deviceID      gwpflumw_deviceValue         gy_datetime
8191                         112,56             2020-07-29 17:00:00
8191                         112,40             2020-07-29 17:05:00
8191                         112,52             2020-07-29 17:10:00 
8191                         112,55             2020-07-29 17:15:00
2

Hi @TubiFex
Sounds like you’re seeing this issue:
https://github.com/metabase/metabase/issues/11907 - upvote by clicking :+1: on the first post

3

Hi flamber,
thanks for your reply and your link, I upvoted it.

Is it possible to deactived the sumation in a certain question?
Or, like my thoughts, to select the average-value of the data of every time-step to avoiding the sumation?

4

@TubiFex Don’t group by gwpflumw_deviceValue.

1 Like
5

Thanks for your reply.
I used the grouping of gwpflumw_deviceValue "automatically" because otherwise I get an error:
"Error: Column »gwpflumw_deviceValue« have to be in the GROUP-BY-clausel or in a Aggregatfunction"
If I use:

SELECT gwpflumw_datetime, AVG(gwpflumw_deviceValue), gwpflumw_deviceID
FROM gwpflumw, gwpflumst
WHERE {{category}} AND {{datum}}
GROUP BY gwpflumw_datetime, gwpflumw_deviceID

I don't get this error because of the "AVG" and no peaks are shown.
Thanks for your help!