Nested GroupBy or for each

[Andreas Schulze]

Hi there,

I am struggling with a computation of mean values from a measurement db. I hope you can give me some hints.

The DB Looks like this


measurement_campaign, sensor, measurement_number, measurement_result
A, S1, 1, 0.1
A, S1, 2, 0.5
A, S1, 3, 0.4
A, S1, 4, 0.3
A, S2, 1, 1.1
A, S2, 2, 1.2
A, S2, 3, 1.1
B, S1, 1, 0.5
B, S1, 2, 0.7
B, S1, 3, 0.6
B, S2, 4, 0.8
B, S2, 1, 0.4
B, S2, 2, 0.3
B, S2, 3, 0.2


For each measurement campaign I would like to calculate the mean for each sensor. An in addition, when less than 4 values are stored, an error flag (like -1) should be set.

The desired result should look like this:

measurement_campaign, sensor, result
A, S1, 0.325
A, S2, -1.0
B, S1, -1.0
B, S2, 0.425

For example in Campaign A the sensor S2 had only 3 result, so there is the flag -1 set. In Campaign B the sensor S1 had the same...

I tried a lot with Group by Statements, but I cannot find a way to solve the Problem.

It would be really nice if you can help me.

Andreas