Xtykutl

The below statement generates several insert statements which have 4 Zeros as shown after after the month(a.timestamp) bit...now I need to do some calculation and update the generated statements with values to replace the 4 Zeros. Statement is below:

select concat('INSERT INTO report_utilization VALUES ('LMD','CCT',',b.circuit_absid,',',year(a.timestamp),',',month(a.timestamp),',0,0,0,0,',round(avg(((a.rx_octets*8)/900)),2),',',round(avg(((a.tx_octets*8)/900)),2),',',round(max(((a.rx_octets*8)/900)),2),',',round(max(((a.tx_octets*8)/900)),2),',0,0,0,0,0);')

from reports.infinera_utilisation a

inner join reports.opsdb_circuit b on (b.name = a.circuit_id)

where year(a.timestamp) = 2017

and month(a.timestamp) = 5

and b.status = 'Operational'

AND b.new_service_type = 'lambda'

group by b.circuit_id;

The calculation I need to make is:

(round(avg(((a.rx_octets*8)/900)) / b.circuit_absid)*100

where a is the "reports.infinera_utilisation" table, and b is the "reports.opsdb_circuit" table. Basically, divide and then multiply by 100 (this in order to calculate % values)

EDIT

I tried the below query but it returned an error Error Code: 1111. Invalid use of group function

update report_utilization ru

set avg_capacity_in = round(avg(((reports.infinera_utilisation.rx_octets*8)/900))) / reports.opsdb_circuit.circuit_absid

where year = 2017 and month = 5 and base_absid = 27957 and service_type = 'LMD';