Can this be done without a View?

A purely academic question.

Suppose I need the determine the total salary by departments, given an employee table that has emp_id, salary, and dept_id.

I can do this easily:

  select dept_id, sum(salary) from emp group by dept_id;

Suppose further my departments are grouped into areas, defined in an area table that has area_id, dept_id. I can easily get the average salary by area as follows:

  select area_id, avg(salary) from area, emp    where emp.dept_id = area.dept_id
   group by area_id;

Suppose, though, that what I really want is the average of the department totals?

I can do this using a view:

  create view dept_sum as
  select dept_id, sum(salary) salary
    from emp group by dept_id;

  select area_id, avg(salary) from area, dept_sum    where dept_sum.dept_id = area.dept_id    group by area_id;

The question is, can I do this with a single self-contained select that doesn't use any views. I don't think I can, at least not in ORACLE. Does anyone have a way to do this, or a proof that this requires a view? Received on Thu Feb 03 1994 - 18:35:25 CET