CODEWITHSUNDEEP
sqldatabaseoracle11grelational-database
Sabir Al Fateh
Sabir Al Fateh

Reputation: 1803

Finding all departments where the total salary is greater than the average of the total salary at all departments

I've found similar types of question asked previously.But those are not perfect that what i want.So, I've to asked here.

I've five tables given as bellow...

department(dept_name,building,budget);
primary key (dept_name)

course(course_id,title,dept_name,credits);
primary key (course_id),
foreign key (dept_name) references department

instructor(id,name,dept_name,salary);
primary key (ID),
foreign key (dept_name) references department

section(course_id,sec_id,semester,year,building,room_number,time_slot_id);
primary key (course_id, sec_id, semester, year),
foreign key (course_id) references course

teaches(id,course_id,sec_id,semester,year);
primary key (ID, course_id, sec_id, semester, year),
foreign key (course_id, sec_id, semester, year) references section,
foreign key (ID) references instructor

Now I am finding all departments where the total salary is greater than the average of the total salary at all departments using this query.

with dept_total (dept_name, value) as
(select dept_name, sum(salary)
from instructor
group by dept_name),
dept_total_avg(value) as
(select avg(value)
from dept_total)
select dept_name
from dept_total, dept_total_avg
where dept_total.value >= dept_total_avg.value;

What will be the equivalent query without using the with clause which is provide the same result ?

Upvotes: 1

Views: 3309

Answers (3)

Sabir Al Fateh
Sabir Al Fateh

Reputation: 1803

Finally, I got the correct query.So,the equivalent query without using the with clause will be this.

select dept_name
from (
    select dept_name, sum(salary) dept_total
    from instructor
    group by dept_name
), (
    select avg(dept_total) dept_total_avg
    from (
        select dept_name, sum(salary) dept_total
        from instructor
        group by dept_name
    )
) where dept_total >= dept_total_avg;

Upvotes: 1

Barry
Barry

Reputation: 3328

Using the HAVING clause will allow this, the following SQL is untested but should work...

SELECT d.dept_name,
    SUM(salary) AS DeptTotalSalary,
    SUM(salary) / COUNT(*) AS DeptAverageSalary
FROM department d
JOIN instructor i ON i.dept_name d.dept_name
GROUP BY d.dept_name
HAVING DeptAverageSalary > DeptTotalSalary

Upvotes: 0

TSungur
TSungur

Reputation: 406

I had no change to test but something like this should work.

SELECT dept_name FROM instructor 
GROUP BY dept_name
HAVING SUM(salary)>=
(SELECT AVG(dept_sum.SumSalary) AS AvgSalary FROM 
    (
        SELECT dept_name, sum(salary) as SumSalary FROM instructor
        GROUP BY dept_name
    ) as dept_sum
)

Upvotes: 0

Related Questions