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.

your attitude is bit too overcomplexed ... you should take a look on GROUP BY clause. Basicaly joining all tables on their respective IDs, and use SUM function together with GROUP BY and order it. And then select the number of highest you want. — Kousalik, Jan 10 '15 at 20:08

Kousalik · Accepted Answer · 2015-01-10T20:34:48.023

2

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;

edited Jan 10 '15 at 20:34

answered Jan 10 '15 at 20:25

Kousalik

3,111
3
24
46

I'm calculating total for a single order getting data from two table and then I want to calculate total order amount for each user . Kindly refer to my tables. – ChathurawinD Jan 10 '15 at 20:29
So the amount of money, not the amount of orders as you're still posting. Right ? – Kousalik Jan 10 '15 at 20:31
Updated the answer accordingly. the idea is still the same. – Kousalik Jan 10 '15 at 20:36
Money is the cost, cost per an order is cost per a single order which is with many products. One person does many orders I want total amount=for all the orders by a single person. – ChathurawinD Jan 10 '15 at 20:38
Yes, that should do the trick. Joning those tables will result in rowset containing one item per line. SUMming them up over each client (done via the group by clause) will give you the number you need. Assuming I got the "number_ordered*(pack_prize/pack_size)" idea correctly. – Kousalik Jan 10 '15 at 20:42
how to do these calculations only for corporate clients. corporate clients have status as corporate in clients table. Thanks a lot for your help. – ChathurawinD Jan 10 '15 at 20:44
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/68564/discussion-between-chathwind-and-kousalik). – ChathurawinD Jan 10 '15 at 20:47
just add where clause – Kousalik Jan 10 '15 at 20:47
Why did you use the subquery when you could have written the same as a main query? – Prashanth Pradeep Jul 23 '20 at 16:47
@PrashanthPradeep your comment looks like unnecessary (and unexplained) showoff. I answered best to my knowledge. If you are aware of a better solution you should post your answer or propose an improvement to the accepted answer for people to come. Bear in mind the question scope is Oracle 11g. – Kousalik Jul 26 '20 at 06:59

Get sum of multiplication of two columns of two tables

1 Answers1