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