SQL multiple and get the sum of different joined tables

Question

I need to get the profit of the orders from the real value price and shipment amount with the quantity of the order using the MySQL query.

Please help to find the result using the join queries. order table is the master table of the order data and the inventory table is our price. shipment charge is for calculating the order shipping charge

For reviewing the price details we need to get the sum with our price and quantity of user ordered with shipment charge

Table: order- the master table to list the order data

order_id	name	order_amount	TOTAL
125	Sunil	125	128
126	Kurian	225	129

Table: order_item - individual order items details

order_id	product_id	quantity	TOTAL
125	12	2
125	13	1

Table: inventory(Individual product actual price )

product_id	price	itemcode
12	30	IKJH
13	40	JUSWK

shipping_table charge of shipment. As there is a chance for adding the shipping cost in multiple tables with the same amount. I need only one value of one order

order_id	charge	itemcode
125	5	IKJH
125	5	IKJH

Expected output

order_id	order_price	actual_price
125	125	105

I tried the following query but the result is wrong.

SELECT a.created_on ,
              a.order_id,
              a.order_amount,
              sum(c.price*b.quantity),
             
FROM `order` AS `a`
INNER JOIN `order_item` AS `b` ON `a`.`order_id` = `b`.`order_id`
INNER JOIN `inventory` AS `c` ON `b`.`product_id` = `c`.`product_id`

INNER JOIN shipping_table AS d ON d.order_id = a.order_id WHERE a.order_id = 125 GROUP BY a.order_id ORDER BY a.order_id

Show desired output for shown sample data. – Akina Feb 18 '21 at 05:38 — Akina, Feb 18 '21 at 05:38

score 0 · Answer 1 · answered Feb 18 '21 at 05:49

You can create a virtual table inside the join for this one.

You can try something like this.

Please check for any performance issues that might arise due to the nature of the virtual table used here. You can make use of the EXPLAIN ANALYZE command in mysql to check for performance issues

SELECT a.order_id, ordertot.tot
FROM order a JOIN
(SELECT order_id, SUM(order_item.quantity * inventory.price) AS tot FROM order_item JOIN inventory ON order_item.product_id = inventory.product_id GROUP BY order_id) AS ordertot
ON a.order_id = ordertot.order_id

SQL multiple and get the sum of different joined tables

1 Answers1