title: MYSQL sum up values across two columns depending on values grouped from other columns
body: <p>I have a network created MYSQL table with following fields:</p>
  
  <pre><code>IP_SRC, IP_DST, BYTES_IN, BYTES_OUT, START_TIME, STOP_TIME
  1.1.1.1 8.8.8.8 1080      540        1580684018  1580684100
  8.8.4.4 1.1.1.1 2000      4000       1580597618  1580597800
  </code></pre>
  
  <p>The TIME values are epoch time ticks and each record is basically one TCP session. IP addresses are stored as "int(10) unsigned" i.e. I use INET_NTOA to get to the "1.1.1.1" string. START_TIME, STOP_TIME, IN_BYTES and OUT_BYTES are also "int(10) unsigned"</p>
  
  <p>I would like formulate a query (or procedure) to return a table with the following fields:</p>
  
  <pre><code>IP_ADDR, TotalOutBytes
  1.1.1.1      12345
  8.8.8.8      83747
  8.8.4.4      2389
  </code></pre>
  
  <p>where for a given IP_ADDR say 1.1.1.1 the TotalOutBytes is the sum of IN_BYTES when IP_DST is 1.1.1.1 and OUT_BYTES when IP_SRC is 1.1.1.1. I would like all IP_SRC and IP_DST from the original table to be accounted for in the new table. I would also want this data to be collected for the last 24 hours.</p>
  
  <p>Here's what I have that works for summing up just the IN_BYTES (and reporting as Mega Bytes in descending order for totals more than 50MB):</p>
  
  <pre><code>select IP_ADDR, format(MBx/1048576,0) AS TotalOutBytes from
    (select inet_ntoa(IP_DST_ADDR) AS IP_ADDR, sum(IN_BYTES) MBx from 
      (select IP_DST_ADDR, IN_BYTES from flowsv4 
          where FIRST_SWITCHED BETWEEN UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY)
              AND UNIX_TIMESTAMP(NOW())) total
  GROUP BY IP_DST_ADDR) NEW WHERE MBx &gt; 50*1048576 ORDER BY MBx DESC;
  </code></pre>