CODEWITHSUNDEEP
sqloracle-database
cas0185
cas0185

Reputation: 23

sql computing average with a group by?

I am learning sql (continuously, it seems!) and I have devised a working solution to the following assignment. I'm just wondering if the same solution can be arrived at with a simpler sql query, perhaps using a group by clause on the view that I created. I was unable to combine group by with an avg() in the select clause. Thoughts?

Problem: Find the name of the managers and the average salary of the employees they manage.

Tables: WORKS - contains three columns: employees (some of which are also managers), company, salary. MANAGES - contains two columns: employee, and that employee's manager.

My temp view - The temp view I created in the query (managers_and_salaries) contains two columns: manager name and the salary of each employee that manager manages. Manager names are, therefore, not unique in this view. Partial contents are as follows; obviously, it's b.s. data:

    this is my temp view (partial).
    a view is NOT required, it's just the way I did it to keep my head straight:
    +------------------+--------+
    | MANAGER_NAME     | SALARY |
    +------------------+--------+
    | Lakshmi Mittal   | 400000 |
    | William Gates III| 300000 |
    | Mark Zuckerberg  | 450000 |
    | Mark Zuckerberg  | 120000 | etc, etc. (11 rows)

My full sql query (surely it doesn't need to be this complicated?)...

    with managers_and_salaries as (select m.manager_name, w.salary
                                   from manages m inner join works w on
                                                  m.employee_name = w.employee_name)
    select manager_name, avg(salary)
    from managers_and_salaries
    where manager_name in (select distinct manager_name
                           from managers_and_salaries)
    group by manager_name;

My partial results... again, it works, I just can't help thinking my sql could be much simpler...

    +------------------+-------------+
    | MANAGER_NAME     | AVG(SALARY) |
    +------------------+-------------+
    | Lakshmi Mittal   |      400000 |
    | Mark Zuckerberg  |      285000 |
    | William Gates III|      300000 | etc, etc, (8 rows)

This is my first post, and I hope my question is clear... I'm not sure if my formatting will be maintained. Sorry if it turns out unreadable. Thanks for any input, surely there's a simpler query that will work. I'm doing this with Oracle, btw, although I suspect that makes little difference here, if any at all.

Upvotes: 1

Views: 3529

Answers (1)

meriton
meriton

Reputation: 70564

from managers_and_salaries
where manager_name in (select distinct manager_name
                       from managers_and_salaries)

That where clause is redundant: The from clause already ensures that only managers from managers_and_salaries are considered.

Once we drop that clause, managers_and_salaries is only used once, and it may be simpler to inline its definition:

select m.manager_name, avg(w.salary)
from manages m 
inner join works w on m.employee_name = w.employee_name
group by manager_name;

Upvotes: 2

Related Questions