Amazon Timetream SQL query to calculate a measures value lower by the average value of that measure in the whole table.

Hello,

I have the following query statement:

WITH avg_database_measure_value AS (SELECT COUNT(DISTINCT time) AS "databaseTotalMeasureCount", ROUND(AVG(measure_value::double),2) AS "databaseAverageMeasureValue" FROM "system_jgsensors_timeseriesDb"."tbl_dms_deviceData" WHERE device_ownerId = 'cbec4e03-16cb-4c75-8499-a780da972d5e' AND device_thingId = '0e5394ae-515b-4e71-abed-fbfffe0781be' AND measure_name = 'measurement_earth_conductivity' ), avg_measure_timepoint AS ( SELECT device_id, measure_name, COUNT(DISTINCT time) AS "measureTimepointCount", ROUND(AVG(measure_value::double),2) AS "measureAverageTimepointValue" FROM "system_jgsensors_timeseriesDb"."tbl_dms_deviceData" WHERE device_ownerId = 'cbec4e03-16cb-4c75-8499-a780da972d5e' AND device_thingId = '0e5394ae-515b-4e71-abed-fbfffe0781be' AND time > ago(1h) GROUP BY device_id, measure_name ) SELECT device_id, measure_name, databaseTotalMeasureCount, databaseAverageMeasureValue, measureTimepointCount, measureAverageTimepointValue FROM avg_database_measure_value, avg_measure_timepoint WHERE measure_name IS NOT NULL AND measure_name = 'measurement_earth_conductivity' AND measureAverageTimepointValue < databaseAverageMeasureValue AND databaseAverageMeasureValue IS NOT NULL AND measureAverageTimepointValue IS NOT NULL ORDER BY measure_name DESC LIMIT 1000

Which returns the following row:

This works well but I am using 1 measurement name, in this case: measurement_earth_conductivity.

The problem I am having is when I try to query using more measure names like for example the following statement:

WITH avg_database_measure_value AS (SELECT COUNT(DISTINCT time) AS "databaseTotalMeasureCount", ROUND(AVG(measure_value::double),2) AS "databaseAverageMeasureValue" FROM "system_jgsensors_timeseriesDb"."tbl_dms_deviceData" WHERE device_ownerId = 'cbec4e03-16cb-4c75-8499-a780da972d5e' AND device_thingId = '0e5394ae-515b-4e71-abed-fbfffe0781be' AND measure_name = 'measurement_earth_conductivity' ), avg_measure_timepoint AS ( SELECT device_id, measure_name, COUNT(DISTINCT time) AS "measureTimepointCount", ROUND(AVG(measure_value::double),2) AS "measureAverageTimepointValue" FROM "system_jgsensors_timeseriesDb"."tbl_dms_deviceData" WHERE device_ownerId = 'cbec4e03-16cb-4c75-8499-a780da972d5e' AND device_thingId = '0e5394ae-515b-4e71-abed-fbfffe0781be' AND time > ago(1h) GROUP BY device_id, measure_name ) SELECT device_id, measure_name, databaseTotalMeasureCount, databaseAverageMeasureValue, measureTimepointCount, measureAverageTimepointValue FROM avg_database_measure_value, avg_measure_timepoint WHERE measure_name IS NOT NULL AND measure_name = 'measurement_earth_conductivity' OR measure_name = 'measurement_earth_nitrogen' OR measure_name = 'measurement_earth_phosphorus' OR measure_name = 'measurement_earth_potassium' OR measure_name = 'measurement_earth_potentialHydrogen' OR measure_name = 'measurement_earth_relativeHumidity' AND measureAverageTimepointValue < databaseAverageMeasureValue AND databaseAverageMeasureValue IS NOT NULL AND measureAverageTimepointValue IS NOT NULL ORDER BY measure_name DESC LIMIT 1000

Then I get the following return:

As you can see the "measure AverageTimepointValue" is correct for the different measures but my problem is with the "databaseAverageMeasureValue" that has the same value for all the rows.

How can I make the "databaseAverageMeasureValue" have the correct average value for its measure?

Thank you. Gianpiero