Member Avatar for jaycastr

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 because: Added code tags. Please use them to format any code that you post.
  • 2 Contributors
  • 1 Reply
  • 149 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by mwasif
Member Avatar for mwasif

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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.