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