Get sum of multiplication of two columns of two tables

See more:

SQL

Oracle

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.