Average number of employees per department, per organization..

[cjard]

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

[WarlockSoul]

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

[TheCPUWizard]

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