this query was too hard for me write. so any help is greatly appreciated

i have three table , a_device, b_devices and a_app

under a_device i have the columns:

under b_device i have the columns:

under c_app i have the columns:

I want to count the number of hostnames in table b_device and group them by department_name

also i need to get the avg_cpu and avg_mem for each department. right now the avg_cpu and avg_mem is for each hostname.

so far i have this:

SELECT a.hostname, b.cpu_avg, b.mem_avg, c.app_name, c.department_name
FROM a_device a, b_device b, c_app c
WHERE a.hostname = b.hostname 
AND a.app_name = c.app_name

i know you have to use the aggregate functions avg() and count() but i dont know where to put them. I am a noob

Try the following query, it is not test

SELECT COUNT(b.hostname), AVG(cpu_avg), AVG(mem_avg), c.department_name 
FROM b_device b 
INNER JOIN a_device a ON b.hostname = a.hostname
INNER JOIN c_app c ON c.app_name = a.app_name
GROUP BY c.department_name