order by group function with group by

user_4763511 0
Viewed 78

I have the following query:

select round(avg(employees.salary)) as "Average salary",
count(1) as "Number of employees",
employees.department_id as "Department ID",
departments.department_name as "Department Name"
from employees, departments
where employees.department_id = departments.department_id
group by employees.department_id, departments.department_name
order by round(avg(employees.salary)) desc;

The returned result is not in the desired order. However, when trying to use alias "Average salary" or 1 the query works just as expected and desired.

The result of the mentioned query:

enter image description here

The result when I use an alias or number:

enter image description here

Why?

group-by sql-order-by oracle sql
1 Answers

Yeah, thats a bug. We look for opportunities to eliminate the cost of group by, and in this case, something is going amiss. You can hint around this as a workaround

SQL> select round(avg(employees.salary)) as "Average salary"
  2  from hr.employees, hr.departments
  3  where employees.department_id = departments.department_id
  4  group by employees.department_id, departments.department_name
  5  order by round(avg(employees.salary)) desc;

Average salary
--------------
          8956
          3476
         19333
          8601
          5760
          4150
         10154
          9500
         10000
          6500
          4400

11 rows selected.

SQL> select /*+ opt_param('_optimizer_aggr_groupby_elim', 'false')*/ round(avg(employees.salary)) as "Average salary"
  2  from hr.employees, hr.departments
  3  where employees.department_id = departments.department_id
  4  group by employees.department_id, departments.department_name
  5  order by round(avg(employees.salary)) desc;

Average salary
--------------
         19333
         10154
         10000
          9500
          8956
          8601
          6500
          5760
          4400
          4150
          3476

11 rows selected.
user_73672930
Related