0

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:
hostname
app_name

under b_device i have the columns:
hostname
cpu_avg
mem_avg

under c_app i have the columns:
app_name
department_name

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

Edited by Ezzaral: Added code tags. Please use them to format any code that you post.

2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by mwasif
0

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
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.