CODEWITHSUNDEEP
sqloracle-database
mrwhite
mrwhite

Reputation: 105

Oracle sql - join and group by clause to pull out a top customers with most orders

edit2: This is what I got :

Tables : https://i.sstatic.net/ML4uA.jpg

I am trying to pull out a top of 25% of the customers who belong to a company and have the most orders.

I tried below query:

Select customer_id,
(
Select Count(order_id) from order 
where commercial_customer.customer_id = order.customer_id
) AS Orders limit by 25/100 * (Count(*) from order) ;

but I'm getting

ORA-00923: FROM keyword not found where expected 00923. 00000 - "FROM keyword not found where expected" *Cause:
*Action: Error at Line: 5 Column: 13

After following the suggestions from comments I got to this query: https://i.sstatic.net/mciC7.jpg

It displays everything right but the Orders which is 3 for both because the Count(*) counts all the orders where customer_id=2.

In order to make this work I would use 

Select customer_id,customer_name,company_code,Orders
from xyz_customer,xyz_commercial, (
Select Count(*) AS Orders from xyz_order
**where customer_id=xyz_comcustomer.customerid**
)
where
rownum<=(select count(*)from XYZ_COMCUSTOMER)/3

but I am getting this:

ORA-00904: "XYZ_COMCUSTOMER"."CUSTOMERID": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 4 Column: 19

Expected result:
Customer_ID, Customer_name,Company_code,Orders
4 Marin 87654321 3
1 Alexa 12345678 1

Upvotes: 2

Views: 206

Answers (3)

Mike
Mike

Reputation: 2005

select customer_id, cnt
  from (
    select customer_id, cnt, ntile(4) over(order by cnt desc) GRP
      from (
        select O.customer_id, count(*) cnt
          from xyz_order O, xyz_commercial_customer CC
         where O.customer_id=CC.customer_id
         group by O.customer_id
      )
  )
 where GRP=1

Window function ntile(4) divides customers into 4 groups. group 1 - 1/4 (25%) customers at descending order number of orders.

Or, using only nested querys (Not recommended):

select *
  from (
        select O.customer_id, count(*) cnt
          from xyz_order O, xyz_commercial_customer CC
         where O.customer_id=CC.customer_id
         group by O.customer_id
         order by cnt desc
       )
 where rownum<=(select count(*) from xyz_commercial_customer)/4

Upvotes: 1

hbourchi
hbourchi

Reputation: 309

Try something like this (with analytical functions)

select *
 from
 (
   select customer_id, rank() over (order by count_orders) rank_orders
   from
   (
     select customer_id, count(*) count_orders
     from order
     group by cusomer_id
   )
 )
 where rank_orders <= round((select count(*) from order) / 4)

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133400

You could use count(*) group by customer_id and a subselect

select a.customer_id, a.customer_name
from customer a
inner join order b on a.customer_id = b.customer_id
group by a.customer_id, a.customer_name
having count(*) > (select count(*) from order )/4;

Upvotes: 0

Related Questions