Get sum of multiplication of two columns of two tables

Question

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.

Answer 1

Just use something like that and adjust it for your particular need. Nothing more complex neccesary here. You'll get the idea.

All you need is aggregate functions. See documentation.

select * from (
SELECT customer_id, sum(number_ordered*(pack_prize/pack_size) as totalvalue
FROM customers 
natural join orders 
natural join items 
natural join products 
group by customer_id order by totalvalue desc
) WHERE rownum <= 5;