CODEWITHSUNDEEP
mysqlsql
Raymond Clayton Rudman
Raymond Clayton Rudman

Reputation: 75

Return one single row with GROUP_CONCAT

I have a problem with my query, I am wanting to group all accounting data together and GROUP_CONCAT the clients site names and site IP's.

I have used GROUP_CONCAT(site_name) as site_name and GROUP_CONCAT(site_ip) as site_ip but the results I get are still incorrect and I am getting duplicate IP's in the GROUP_CONCAT column.

I need a single row of data per client with the accounted traffic SUM/Grouped together for each of the site IP's belonging to them.

My query is:

SELECT 
SUM(upload_bytes) as upload_bytes, 
SUM(download_bytes) as download_bytes, 
SUM(upload_bytes + download_bytes) as totalbytes, 
package_id, 
username, 
userid, 
networkaccess, 
packagename, 
speedlimit, 
threshold, 
throttlelimit, 
extendeddata, 
datalimitamount, 
accountingdays, 
GROUP_CONCAT(site_name) as site_name, 
GROUP_CONCAT(site_ip) as site_ip 
FROM 
(
    (
        SELECT 
            ip_accounting.src_address as site_ip, 
            SUM(ip_accounting.bytes) AS upload_bytes, 
            0 as download_bytes, 
            clients.username, 
            clients.userid, 
            clients.networkaccess, 
            clients.extendeddata, 
            data_packages.package_id, 
            data_packages.packagename, 
            data_packages.speedlimit, 
            data_packages.threshold, 
            data_packages.throttlelimit, 
            data_packages.datalimitamount, 
            data_packages.accountingdays, 
            client_site_ip.site_name 
        FROM 
            ip_accounting 
            join client_site_ip on client_site_ip.site_ip = ip_accounting.src_address 
            JOIN clients ON client_site_ip.userid = clients.userid 
            join data_packages on data_packages.package_id = clients.datapackage 
        WHERE 
            dst_address NOT BETWEEN INET_NTOA('192.168.0.1') 
            AND INET_NTOA('192.168.255.254') 
            and timeanddate BETWEEN SUBDATE(
                CURRENT_TIMESTAMP(), 
                INTERVAL data_packages.accountingdays DAY
            ) 
            AND CURRENT_TIMESTAMP() 
        GROUP BY 
            src_address
    ) 
    UNION ALL 
        (
            SELECT 
                ip_accounting.dst_address as site_ip, 
                0 AS upload_bytes, 
                SUM(ip_accounting.bytes) as download_bytes, 
                clients.username, 
                clients.userid, 
                clients.networkaccess, 
                clients.extendeddata, 
                data_packages.package_id, 
                data_packages.packagename, 
                data_packages.speedlimit, 
                data_packages.threshold, 
                data_packages.throttlelimit, 
                data_packages.datalimitamount, 
                data_packages.accountingdays, 
                client_site_ip.site_name 
            FROM 
                ip_accounting 
                join client_site_ip on client_site_ip.site_ip = ip_accounting.dst_address 
                JOIN clients ON client_site_ip.userid = clients.userid 
                join data_packages on data_packages.package_id = clients.datapackage 
            WHERE 
                src_address NOT BETWEEN INET_NTOA('192.168.0.1') 
                AND INET_NTOA('192.168.255.254') 
                and timeanddate BETWEEN SUBDATE(
                    CURRENT_TIMESTAMP(), 
                    INTERVAL data_packages.accountingdays DAY
                ) 
                AND CURRENT_TIMESTAMP() 
            GROUP BY 
                dst_address
        )
) a 
GROUP BY 
site_ip 
ORDER BY 
INET_ATON(site_ip)

My current result looks like:

enter image description hereenter image description here

Upvotes: 1

Views: 459

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133380

you should group by the non aggregated columns (and not for the column you use in aggregation function) eg: 

  SELECT 
  SUM(upload_bytes) as upload_bytes, 
  SUM(download_bytes) as download_bytes, 
  SUM(upload_bytes + download_bytes) as totalbytes, 
  package_id, 
  username, 
  userid, 
  networkaccess, 
  packagename, 
  speedlimit, 
  threshold, 
  throttlelimit, 
  extendeddata, 
  datalimitamount, 
  accountingdays, 
  GROUP_CONCAT(DISTINCT site_name) as site_name, 
  GROUP_CONCAT(DISTINCT site_ip) as site_ip 
  FROM 
  (
      (
          SELECT 
              ip_accounting.src_address as site_ip, 
              SUM(ip_accounting.bytes) AS upload_bytes, 
              0 as download_bytes, 
              clients.username, 
              clients.userid, 
              clients.networkaccess, 
              clients.extendeddata, 
              data_packages.package_id, 
              data_packages.packagename, 
              data_packages.speedlimit, 
              data_packages.threshold, 
              data_packages.throttlelimit, 
              data_packages.datalimitamount, 
              data_packages.accountingdays, 
              client_site_ip.site_name 
          FROM 
              ip_accounting 
              join client_site_ip on client_site_ip.site_ip = ip_accounting.src_address 
              JOIN clients ON client_site_ip.userid = clients.userid 
              join data_packages on data_packages.package_id = clients.datapackage 
          WHERE 
              dst_address NOT BETWEEN INET_NTOA('192.168.0.1') 
              AND INET_NTOA('192.168.255.254') 
              and timeanddate BETWEEN SUBDATE(
                  CURRENT_TIMESTAMP(), 
                  INTERVAL data_packages.accountingdays DAY
              ) 
              AND CURRENT_TIMESTAMP() 
          GROUP BY 
              src_address
      ) 
      UNION ALL 
          (
              SELECT 
                  ip_accounting.dst_address as site_ip, 
                  0 AS upload_bytes, 
                  SUM(ip_accounting.bytes) as download_bytes, 
                  clients.username, 
                  clients.userid, 
                  clients.networkaccess, 
                  clients.extendeddata, 
                  data_packages.package_id, 
                  data_packages.packagename, 
                  data_packages.speedlimit, 
                  data_packages.threshold, 
                  data_packages.throttlelimit, 
                  data_packages.datalimitamount, 
                  data_packages.accountingdays, 
                  client_site_ip.site_name 
              FROM 
                  ip_accounting 
                  join client_site_ip on client_site_ip.site_ip = ip_accounting.dst_address 
                  JOIN clients ON client_site_ip.userid = clients.userid 
                  join data_packages on data_packages.package_id = clients.datapackage 
              WHERE 
                  src_address NOT BETWEEN INET_NTOA('192.168.0.1') 
                  AND INET_NTOA('192.168.255.254') 
                  and timeanddate BETWEEN SUBDATE(
                      CURRENT_TIMESTAMP(), 
                      INTERVAL data_packages.accountingdays DAY
                  ) 
                  AND CURRENT_TIMESTAMP() 
              GROUP BY 
                  dst_address
          )
  ) a 
  GROUP BY 
      package_id, 
      username, 
      userid, 
      networkaccess, 
      packagename, 
      speedlimit, 
      threshold, 
      throttlelimit, 
      extendeddata, 
      datalimitamount, 
      accountingdays

Upvotes: 1

Related Questions