I have product table like this
PRODUCT_ID PACK_SIZE PACK_PRIZE
3000 5 2.5
3001 5 2.5
3002 5 2.5
3003 5 2.5
Order table
order_id client_id
75001 1024
75002 1033
75003 1030
ITEMS Table
ORDER_ID PRODUCT_ID NUMBER_ORDERED
75001 3936 2
75001 3557 5
75001 3012 3
75001 3236 4
Client Table
CLIENT_ID LAST_NAME STATUS
1021 Smith private
1022 Williams corporate
1023 Browne private
1024 Tinsell corporate
These are sample data I just added these just to show sample data.
Here I want to select top 5 clients who is having highest total orders amount.
I want to select the clients whose status is corporate and who are having the highest amount of orders.
In other words i want to select client_id s of clients whose having higher total order amount.
Here I'm trying to achieve it like this.
WITH CTE as ( SELECT ORDERS.ORDER_ID, PRODUCTS.PACK_PRIZE, PRODUCTS.PACK_SIZE, ITEMS.NUMBER_ORDERED,
CLIENTS.STATUS,CLIENTS.CLIENT_ID,CLIENTS.FIRST_NAME,CLIENTS.LAST_NAME
FROM ORDERS INNER JOIN
ITEMS
ON ORDERS.ORDER_ID = ITEMS.ORDER_ID INNER JOIN
PRODUCTS
ON ITEMS.PRODUCT_ID = PRODUCTS.PRODUCT_ID
INNER JOIN
CLIENTS
ON ORDERS.CLIENT_ID = CLIENTS.CLIENT_ID
WHERE CLIENTS.STATUS='corporate')
SELECT CLIENT_ID,FIRST_NAME,LAST_NAME,ORDER_ID,((PACK_PRIZE/PACK_SIZE) * NUMBER_ORDERED)AS Total
FROM (SELECT CTE.*
FROM CTE
ORDER BY SUM(PACK_PRIZE/PACK_SIZE) * NUMBER_ORDERED DESC
) t
WHERE rownum <= 5;
But here I'm getting the highest orders, so orders by same client comes in this too.
I want to select the top 5 customers who is having highest total order amount.
**All the clients who are corporate should be selected.
Then total amount of all the orders of each client should be calculated
and I want to select the clients with highest 10 values.**
I'm using oracle 11g.