Avg number of employees per department per organization?

[cjard]

I'd do this:


SELECT
org,
avg(cnt_dep)
FROM
(
SELECT
org,
count(*) as cnt_dep
FROM
emp
GROUP BY
org, dep
)
GROUP BY
org

Would you do any different?

assume we had a table with:

ibm, marketing, 3
ibm, dev, 4
ibm, hr, 2
ora, research, 2
ora, pr, 3
ora, marketing 7


and were seeking:
org, avg_emp_per_dep
ibm, 3
ora, 4

[tamilselvan]

No need to write in-line view.



SQL> select * from mytable;

ORG DEPTNAME TOT_DEPT_EMPL
--- ---------- -------------
ibm marketing 3
ibm sales 6
ibm accounts 12
ora marketing 6
ora sales 10
ora accounts 20

6 rows selected.

SQL> select org, avg(tot_dept_empl) from mytable
2 group by org;

ORG AVG(TOT_DEPT_EMPL)
--- ------------------
ora 12
ibm 7