0

Having trouble with a join and grouping values. Historically I have been able to pull sums of job sizes by client_host_name - using one table. Now we have a new table that adds a new value 'service_group' and I need to group aggregate sum totals of clients grouped by service_group.

I have 2 tables to query.

Table 1: apt_v_nbu_job_detail

  • client_id (unique id of client machine)
  • client_display_name (name of client)
  • kilobytes (size of image for client)
  • expiration_date (date image expires)

Table 2: apt_v_server_attribute

  • host_id (match with apt_v_nbu_job_detail.client_id)
  • service_group (value I need to group by)

Goal is to pull the client totals (kilobytes), then group the aggregate sum of all clients per service_group.

Example:
ServiceGroup          Total_GB
Core.ACS              3,109
Core.Audit            1,234
Core.Backup           1,567




SELECT j.client_host_name, sum(j.nbr_of_files), trunc(sum(j.kilobytes)/1024/1024) as "size(GB)"
FROM apt_v_nbu_job_detail j
INNER JOIN apt_v_server_attribute sa ON j.client_id = sa.host_id
--WHERE sa.service_group IN ('Core.ACS', 'Core.Audit', 'Core.Backup', 'Core.IDM', 'Core.Provisioning', 'Core.Proxies', 'Email.Zimbra');
WHERE j.client_id in (${hosts})
AND summary_status is not null
--AND expiration_date <= sysdate
GROUP BY j.client_host_name
--GROUP BY sa.service_group
ORDER BY trunc(sum(kilobytes)/1024/1024) DESC

Currently, this just pulls all clients and their total kilobytes grouped by client name. When I try to group by service_group it gives me "ORA-00979: not a GROUP BY expression".

My SQL skills are BASIC at best, so this has been a challenge. TIA

For context, this is what a 'SELECT * FROM apt_v_server_attribute' gives:

host_id host_name   service_group
300,108 netbackup.lab.example.com   Core.Backup
300,109 dc1-chatt-lab-aaa-dhcp-001.lab.example.com  Services.GPON.DHCP-RADIUS
300,110 dc1-chatt-lab-aaa-dhcp-002.lab.example.com  Services.GPON.DHCP-RADIUS
300,111 dc1-chatt-lab-aaa-dh4-001.lab.example.com   Services.GPON.DHCP-RADIUS
300,112 dc1-chatt-lab-aaa-dh4-002.lab.example.com   Services.GPON.DHCP-RADIUS
300,113 dc1-chatt-lab-aaa-dh6-001.lab.example.com   Services.GPON.DHCP-RADIUS
300,114 dc1-chatt-lab-aaa-dh6-002.lab.example.com   Services.GPON.DHCP-RADIUS
300,115 dc1-chatt-lab-aaa-ldp-001.lab.example.com   Services.GPON.DHCP-RADIUS
300,116 dc1-chatt-lab-aaa-ldp-002.lab.example.com   Services.GPON.DHCP-RADIUS
300,117 dc1-chatt-lab-aaa-mgt-003.lab.example.com   Services.GPON.DHCP-RADIUS
300,118 dc1-chatt-lab-aaa-rad-001.lab.example.com   Services.GPON.DHCP-RADIUS
300,119 dc1-chatt-lab-aaa-rad-002.lab.example.com   Services.GPON.DHCP-RADIUS
300,120 dc1-chatt-lab-aaa-rad-003.lab.example.com   Services.GPON.DHCP-RADIUS
300,121 dc1-chatt-lab-aaa-rad-004.lab.example.com   Services.GPON.DHCP-RADIUS
300,122 dc1-chatt-lab-aaa-rds-001.lab.example.com   Services.GPON.DHCP-RADIUS
300,123 dc1-chatt-lab-aaa-rds-002.lab.example.com   Services.GPON.DHCP-RADIUS
300,124 dc1-chatt-lab-aaa-rds-003.lab.example.com   Services.GPON.DHCP-RADIUS
300,125 dc1-chatt-lab-aaa-rds-004.lab.example.com   Services.GPON.DHCP-RADIUS
300,126 dc1-chatt-lab-aaa-rds-005.lab.example.com   Services.GPON.DHCP-RADIUS
300,127 dc1-chatt-lab-aaa-rds-006.lab.example.com   Services.GPON.DHCP-RADIUS
300,128 prov1.lab.example.com   Core.Provisioning
300,129 mta-db.lab.example.com  Email.Zimbra
2
  • Hi, and welcome to dba.se! Set up a fiddle with some sample data and your desired result. Start with ORDER BY 3.
    – Vérace
    Commented Apr 29 at 20:37
  • If you want to group by sa.service_group you need to change SELECT j.client_host_name, to SELECT sa.service_group, Commented Apr 30 at 11:16

2 Answers 2

0

To me, it looks as if you'll have to rearrange current query so that you

  • replace client_host_name with service_group in both select column list and group by clause
    • or, if you need both of them, use both of them in group by

Here's an example (I also added missing columns to the 1st table; you missed them).

Sample tables:

SQL> WITH
  2     apt_v_nbu_job_detail (client_id,
  3                           client_display_name,
  4                           kilobytes,
  5                           expiration_date,
  6                           nbr_of_files,
  7                           client_host_name)
  8     AS
  9        (SELECT 1, 'x', 100, SYSDATE, 1000, 'xyz' FROM DUAL
 10         UNION ALL
 11         SELECT 2, 'y', 200, SYSDATE, 3000, 'uwz' FROM DUAL),
 12     apt_v_server_attribute (host_id, service_Group)
 13     AS
 14        (SELECT 1, 'aaa' FROM DUAL
 15         UNION ALL
 16         SELECT 2, 'bbb' FROM DUAL)

Query begins here:

 17    SELECT sa.service_group,
 18           SUM (j.nbr_of_files),
 19           TRUNC (SUM (j.kilobytes) / 1024 / 1024) AS "size(GB)"
 20      FROM apt_v_nbu_job_detail j
 21           INNER JOIN apt_v_server_attribute sa ON j.client_id = sa.host_id
 22     WHERE j.client_id IN (1, 2, 3)   --($ { hosts })
 23  --         AND summary_status IS NOT NULL
 24  GROUP BY sa.service_group
 25  ORDER BY TRUNC (SUM (kilobytes) / 1024 / 1024) DESC;

SER SUM(J.NBR_OF_FILES)   size(GB)
--- ------------------- ----------
bbb                3000          0
aaa                1000          0

SQL>

If you need both columns, then modify query to:

 17    SELECT sa.service_group,
 18           j.client_host_name,
 19           SUM (j.nbr_of_files),
 20           TRUNC (SUM (j.kilobytes) / 1024 / 1024) AS "size(GB)"
 21      FROM apt_v_nbu_job_detail j
 22           INNER JOIN apt_v_server_attribute sa ON j.client_id = sa.host_id
 23     WHERE j.client_id IN (1, 2, 3)   --($ { hosts })
 24  --         AND summary_status IS NOT NULL
 25  GROUP BY sa.service_group, j.client_host_name
 26  ORDER BY TRUNC (SUM (kilobytes) / 1024 / 1024) DESC;

SER CLI SUM(J.NBR_OF_FILES)   size(GB)
--- --- ------------------- ----------
bbb uwz                3000          0
aaa xyz                1000          0

SQL>
5
  • Thank you @Littlefoot, I was way off then! I will give this a try.
    – Derek
    Commented Apr 30 at 12:19
  • So I tried the first example, and I have some tweaking to do. Gives me error: "The query failed to evaluate.internal failure". One thing to note, service_group values are in the apt_v_server_attribute source, and not in apt_v_nbu_job_detail so that may be an issue.
    – Derek
    Commented Apr 30 at 12:40
  • So move it into appropriate table (use a different alias). As of error itself: it doesn't sound like an Oracle error, I can't help with that.
    – Littlefoot
    Commented Apr 30 at 12:57
  • Even a simple group by fails validation for some reason when I use apt_v_server_attribute as source: SELECT service_group, host_id FROM apt_v_server_attribute WHERE service_group is not null GROUP BY service_group I have a LOT to learn apparently lol If I nix the GROUP BY it pulls the data.
    – Derek
    Commented Apr 30 at 13:59
  • Progress: This appears to be working SELECT sa.service_group, SUM(j.kilobytes)/1024/1024 as "size(GB)" FROM apt_v_server_attribute sa INNER JOIN apt_v_nbu_job_detail j ON sa.host_id = j.client_id GROUP BY sa.service_group ORDER BY trunc(sum(j.kilobytes)/1024/1024) DESC
    – Derek
    Commented Apr 30 at 15:10
0

Here is what worked for me:

SELECT sa.service_group, 
SUM(j.kilobytes)/1024/1024 as "Total_Unexpired_Data(GB)",
SUM(CASE WHEN expiration_date BETWEEN sysdate AND sysdate+30 THEN kilobytes/1024/1024 END) less_than_30,
SUM(CASE WHEN expiration_date BETWEEN sysdate+30 AND sysdate+90 THEN kilobytes/1024/1024 END) bt_30_and_90,
SUM(CASE WHEN expiration_date BETWEEN sysdate+90 AND sysdate+365.25 THEN kilobytes/1024/1024 END) bt_90_and_1yr,
SUM(CASE WHEN expiration_date BETWEEN sysdate+365.25 AND sysdate+1825 THEN kilobytes/1024/1024 END) bt_1_and_5yrs,
SUM(CASE WHEN expiration_date > sysdate+1825 THEN kilobytes/1024/1024 END) over_5yrs

FROM apt_v_server_attribute sa
INNER JOIN apt_v_nbu_job_detail j ON sa.host_id = j.client_id
WHERE client_id IN (${hosts})
AND j.summary_status IS NOT NULL
--AND start_date BETWEEN ${startDate} AND ${endDate}
--AND expiration_date > sysdate
GROUP BY sa.service_group
ORDER BY trunc(sum(j.kilobytes)/1024/1024) DESC
3
  • That's, basically, the 1st query I posted - SERVICE_GROUP in both SELECT column list and GROUP BY clause.
    – Littlefoot
    Commented Apr 30 at 19:04
  • 1
    Yeah it took me a while to get past the strange errors in the template designer UI. After about a dozen iterations of your suggestion it finally worked. How I will never know, but I have the data needed now. Thanks again!
    – Derek
    Commented May 2 at 13:02
  • Hahah, miracles happen from time to time :) I'm glad you made it work.
    – Littlefoot
    Commented May 2 at 18:31

Not the answer you're looking for? Browse other questions tagged or ask your own question.