Average number of employees per department, per organization..
Any comments on the best way to do a multi level average like this?
I'd do it like:
SELECT
org,
avg(cnt_dep)
FROM
(
SELECT
org,
count(*) as cnt_dep
FROM
emp
GROUP BY
org, dep
)
GROUP BY
org
Any other offerings?
I've tried
AVG(COUNT(*) OVER(PARTITION BY org, dep))
but the group function is nested too deeply
[473 byte] By [
cjard] at [2007-11-20 7:26:23]
# 1 Re: Average number of employees per department, per organization..
Hi,
The example you gave is going to do the average of the average employees per department.
For this query to work you need to give your derived table a name e.g.
Select
[OrgData].[org],
avg([OrgData].[cnt_dep])
From
(
Select
[emp].[org],
[emp].[dep],
Count(*) as 'cnt_dep'
From [dbo].[emp]
Group By
[emp].[org], [emp].[dep]
) [OrgData]
Group By
[OrgData].[org]
Hope this helps
# 2 Re: Average number of employees per department, per organization..
One way is to use a combination of Queries...
Select "Dept" as Category, DeptID as Key, count(EmpID) as Count from xxx groupby DepyID
Union
Select "Org" as Category, ORGDD as Key, count(EmpID) as Count from xxx groupby OrgIDID
