CODEWITHSUNDEEP
sqloracle-database
WaelT
WaelT

Reputation: 25

Max value of count in oracle

I have these tables, Orders Table:

Name       Null?    Type  
ORDER_ID  NOT NULL  NUMBER(5)  
CUSTOMER_ID         NUMBER(8)  
SHIPMENT_METHOD_ID  NUMBER(2)

and Shipment_method Table:

Name               Null?      Type  
SHIPMENT_METHOD_ID  NOT NULL  NUMBER(2)  
SHIPMENT_DESCRIPTION          VARCHAR2(80)

I'm trying to get the most used shipping method based on the orders, and I'm kind of a beginner here so I need some help. I'm thinking if it's possible to have MAX(count(order_id)) but how can I do that for each shipment_method_id?

Upvotes: 0

Views: 33375

Answers (4)

halfbit
halfbit

Reputation: 3464

As a beginner, you may find using with useful which allows to have kind of named intermediate results:

with STATS as (select SHIPMENT_METHOD_ID, count(*) as N 
               from ORDERS group by SHIPMENT_METHOD_ID)
, MAXIMUM as (select max(N) as N from STATS)
select SHIPMENT_METHOD_ID, SHIPMENT_DESCRIPTION 
from STATS
join MAXIMUM on STATS.N = MAXIMUM.N
natural join SHIPMENT_METHOD

Upvotes: 0

DrabJay
DrabJay

Reputation: 3099

Here is a method that allows for more than one Shipment Method having the same maximum number of Orders.

SELECT shipment_method_id
      ,shipment_description
      ,orders
FROM
   (SELECT shipment_method_id
          ,shipment_description
          ,orders
          ,rank() OVER (ORDER BY orders DESC) orders_rank
    FROM
       (SELECT smm.shipment_method_id
              ,smm.shipment_description
              ,count(*) orders
        FROM orders odr
             INNER JOIN shipment_method smm
               ON (smm.shipment_method_id = odr.shipment_method_id)
        GROUP BY smm.shipment_method_id
                ,smm.shipment_description
       )
   )
WHERE orders_rank = 1

Upvotes: 2

Brian DeMilia
Brian DeMilia

Reputation: 13248

This is another approach:

select shipment_method_id, shipment_description, count(*) as num_orders
  from orders
  join shipment_method
 using (shipment_method_id)
 group by shipment_method_id, shipment_description
having count(*) = (select max(count(order_id))
                     from orders
                    group by shipment_method_id)

Upvotes: 4

Barmar
Barmar

Reputation: 780994

You don't need MAX, you just need to return the top row

SELECT Shipment_Method_Desc
FROM (
    SELECT Shipment_Method_ID, Shipment_Method_Desc, COUNT(*) AS ct
    FROM Shipment_Method s
    JOIN Orders o ON s.Shipment_Method_ID = o.Shipment_Method_ID
    GROUP BY Shipment_Method_ID
    ORDER BY ct DESC)
WHERE ROWNUM = 1

If you're using Oracle 12c or newer, you can use the row limiting clause instead of the subquery:

SELECT Shipment_Method_ID, Shipment_Method_Desc, COUNT(*) AS ct
FROM Shipment_Method s
JOIN Orders o ON s.Shipment_Method_ID = o.Shipment_Method_ID
GROUP BY Shipment_Method_ID
ORDER BY ct DESC
FETCH FIRST 1 ROW ONLY

Upvotes: 2

Related Questions