CODEWITHSUNDEEP
sqloracle-database
user1692342
user1692342

Reputation: 5237

Group function is nested too deeply SQL Error

I have a table which looks like this:

+-----------------+--------------+
| Field           | Type         |
+-----------------+--------------+
| orderNumber (PK)| int          |
| orderDate       | date         |
| requiredDate    | date         |
| shippedDate     | date         |
| status          | char(15)     |
| comments        | char(200)    |
| customerNumber  | int          |
+-----------------+--------------+

I need to return the customerNumber which has maximum number of orders.

I tried the following command:

SELECT customerNumber FROM ORDERS WHERE customerNumber IN (SELECT customerNumber FROM ORDERS HAVING MAX(COUNT(customerNumber)) GROUP BY customerNumber);

I think an error: group function is nested too deeply

Upvotes: 1

Views: 5801

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

One way to do it is using ctes, where you get the count of orders in the first cte, then select the maximum value. Finally join them to get the customer with the maximum orders.

with ordercount as (select customernumber, count(distinct ordernumber) ordercount
                   from orders 
                   group by customernumber)
,maxorders as (select max(ordercount) maxcount from ordercount)
select o.customernumber
from ordercount o 
join maxorders m on m.maxcount = o.ordercount

Upvotes: 0

MT0
MT0

Reputation: 167972

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE ORDERS (
  orderNumber     int PRIMARY KEY,
  orderDate       date,
  requiredDate    date,
  shippedDate     date,
  status          char(15),
  comments        char(200),
  customerNumber  int
);

INSERT INTO ORDERS ( ORDERNUMBER, CUSTOMERNUMBER ) VALUES ( 1, 1 );
INSERT INTO ORDERS ( ORDERNUMBER, CUSTOMERNUMBER ) VALUES ( 2, 1 );
INSERT INTO ORDERS ( ORDERNUMBER, CUSTOMERNUMBER ) VALUES ( 3, 2 );
INSERT INTO ORDERS ( ORDERNUMBER, CUSTOMERNUMBER ) VALUES ( 4, 2 );
INSERT INTO ORDERS ( ORDERNUMBER, CUSTOMERNUMBER ) VALUES ( 5, 3 );
INSERT INTO ORDERS ( ORDERNUMBER, CUSTOMERNUMBER ) VALUES ( 6, 4 );

Query 1 - If you only want to get a single customer:

SELECT CUSTOMERNUMBER
FROM   (
  SELECT CUSTOMERNUMBER,
         COUNT( ORDERNUMBER ) AS num_orders
  FROM   ORDERS
  GROUP BY CUSTOMERNUMBER
  ORDER BY num_orders DESC
)
WHERE ROWNUM = 1

Results:

| CUSTOMERNUMBER |
|----------------|
|              1 |

Query 2 - If you want to get all customers with the highest number of orders:

SELECT CUSTOMERNUMBER
FROM (
  SELECT CUSTOMERNUMBER,
         RANK() OVER ( ORDER BY NUM_ORDERS DESC ) AS RNK
  FROM   (
    SELECT CUSTOMERNUMBER,
           COUNT( ORDERNUMBER ) AS num_orders
    FROM   ORDERS
    GROUP BY CUSTOMERNUMBER
    ORDER BY num_orders DESC
  )
)
WHERE RNK = 1

Results:

| CUSTOMERNUMBER |
|----------------|
|              1 |
|              2 |

Upvotes: 1

Related Questions