how to sum a list of max values group by

Question

I'm trying to sum the max values of a column named value1 grouped by sensor id value so I want the last greater value from each sensor id.

Using this code I get a column with rows with the max results for each sensor. But I dont know how to sum this values.

select max(value1) from `digiwork_esp-data`.SensorData group by sensor

I need to sum this individual values of the result into a total. I tried this, but it is returning a big number (not correct):

select sum(value1) 
from `digiwork_esp-data`.SensorData
where value1 = any (SELECT max(value1) FROM `digiwork_esp-data`.SensorData group by sensor)

score 1 · Accepted Answer · answered Dec 11 '19 at 08:59

1

You can directly sum the result of the subquery.

select sum(t1.val) from
    (select max(value1) val FROM digiwork_esp-data.SensorData group by sensor) t1

answered Dec 11 '19 at 08:59

Ed Bangga

thanks so much Metal.. soooo much im new to this.... your code worked perfect! i just had to add the `` to the database like this select sum(t1.val) from (select max(value1) val FROM ` digiwork_esp-data `.SensorData group by sensor) t1 but with no spaces... thanks so much i tried to find in internet this solution for hours ! have a great week METAL! just a question for the sake of learning and leave this knowledge for whoever find this post... how is it called the technic used? agregation? join? – Psyplant Psytrance Dec 11 '19 at 15:07

1 Answers1