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';









share|improve this question




























    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';









    share|improve this question


























      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';









      share|improve this question















      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






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Nov 8 at 10:36

























      asked Nov 8 at 10:08









      Omar

      1118




      1118





























          active

          oldest

          votes











          Your Answer






          StackExchange.ifUsing("editor", function () {
          StackExchange.using("externalEditor", function () {
          StackExchange.using("snippets", function () {
          StackExchange.snippets.init();
          });
          });
          }, "code-snippets");

          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "1"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          convertImagesToLinks: true,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: 10,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














           

          draft saved


          draft discarded


















          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





































          active

          oldest

          votes













          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes
















           

          draft saved


          draft discarded



















































           


          draft saved


          draft discarded














          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




















































































          Popular posts from this blog

          Schultheiß

          Verwaltungsgliederung Dänemarks

          Liste der Kulturdenkmale in Wilsdruff