sql - Group by, Subquery and Nested group by function in the Having clause -

sql - Group by, Subquery and Nested group by function in the Having clause -

trying display details of staff fellow member amount of orders, in oracle there error stating grouping function 'nested deeply'.

select c.staff_no, s.first_name, s.last_name, max(count(*)) "number of orders" cust_order c, staff s c.staff_no = s.staff_no grouping c.staff_no, s.first_name, s.last_name having max(count(*)) > (select max((count(*)) cust_order c, staff s c.staff_no = s.staff_no);

instead, utilize analytic functions:

select staff_no, first_name, last_name, "number of orders" (select c.staff_no, s.first_name, s.last_name, count(*) "number of orders", max(count(*)) on () maxcount cust_order c bring together staff s on c.staff_no = s.staff_no grouping c.staff_no, s.first_name, s.last_name ) cs "number of orders" = maxcount;

this should have improve performance.

sql oracle nested having clause