mysql calculated field complicated statement
up vote
-2
down vote
favorite
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';
mysql sql-update calculated-columns insert-update calculated-field
add a comment |
up vote
-2
down vote
favorite
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';
mysql sql-update calculated-columns insert-update calculated-field
add a comment |
up vote
-2
down vote
favorite
up vote
-2
down vote
favorite
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';
mysql sql-update calculated-columns insert-update calculated-field
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';
mysql sql-update calculated-columns insert-update calculated-field
mysql sql-update calculated-columns insert-update calculated-field
edited Nov 8 at 10:36
asked Nov 8 at 10:08
Omar
1118
1118
add a comment |
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53205481%2fmysql-calculated-field-complicated-statement%23new-answer', 'question_page');
}
);
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password