title: Get sum of multiplication of two columns of two tables
body: <p>I have product table like this </p>
  
  <pre><code>PRODUCT_ID  PACK_SIZE   PACK_PRIZE
   3000           5       2.5
   3001           5       2.5
   3002           5       2.5
   3003           5       2.5
  </code></pre>
  
  <p>Order table</p>
  
  <pre><code>order_id     client_id 
  75001   1024
  75002   1033
  75003   1030
  </code></pre>
  
  <p>ITEMS Table</p>
  
  <pre><code>ORDER_ID    PRODUCT_ID  NUMBER_ORDERED
   75001  3936    2
   75001  3557    5
   75001  3012    3
   75001  3236    4
  </code></pre>
  
  <p>Client Table</p>
  
  <pre><code>CLIENT_ID   LAST_NAME    STATUS
   1021    Smith          private
   1022    Williams       corporate
   1023    Browne         private
   1024    Tinsell        corporate
  </code></pre>
  
  <p>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.</p>
  
  <p>I want to select the clients whose status is corporate and who are having the highest amount of orders.</p>
  
  <p>In other words i want to select client_id s of clients whose having higher total order amount.</p>
  
  <p>Here I'm trying to achieve it like this. </p>
  
  <pre><code>   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 &lt;= 5;
  </code></pre>
  
  <p>But here I'm getting the highest orders, so orders by same client comes in this too.</p>
  
  <p>I want to select the top 5 customers who is having highest total order amount.</p>
  
  <p><strong>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.</strong>
  I'm using oracle 11g.</p>