Order sum and order value is not fetching properly in mysql

Question

I have 4 tables.

table_orders

order_id
customer_id
order_datetime
order_payment_type
order_delivery_date
delivery_time_slot  

table_order_details

order_id
product_id
varient_id
quantity
product_mrp
product_sell_price
product_name

table_order_status

order_id
status_id
status_datetime

table_order_status_values

value_id
value_desc  

I want to get order id, order amount, order date, order quantity, order time, value_desc.

I am running this query.

SELECT oo.order_id, oo.amount, oo.date, oo.quantity, oo.time, value_desc
FROM 
(
     SELECT s.order_id,  SUM(OD.product_sell_price * OD.quantity) as amount, 
       DATE_FORMAT(o.order_datetime, '%d/%m/%Y') as date, SUM(OD.quantity) 
       as quantity, TIME(o.order_datetime) as time, MAX( status_id ) as laststatus
     FROM table_orders o
     INNER JOIN table_order_details AS OD ON o.order_id = OD.order_id
     INNER JOIN table_order_status s ON s.order_id = o.order_id
     GROUP BY o.order_id
)oo

INNER JOIN table_order_status_values ON value_id = laststatus

order by order_id DESC

sample Data:

Table_orders

1 1 2015:12:12:19:42:47 1 2015:12:14 1

table_order_details

1 12 3 1 21.00 20.00 abcd
1 13 2 2 100.00 90.00 efgh

table_order_status

1 1 2015:12:12:19:42:47
1 2 2015:12:12:20:42:47

table_order_status_values

1 NEW ORDER
2 CONFIRM
3 Delivered

With the above query Output is:

1 400.00 12:12:2015 6 19:42:47 CONFIRM

But expected output is:

1 200.00 12:12:2015 3 19:42:47 CONFIRM 

I am getting order amount and order quantity values twice (or thrice) based on number of status for given order.

How to correct this? Any help will be highly appreciated.

Answer 1

You problem is in this part:

SELECT s.order_id,  SUM(OD.product_sell_price * OD.quantity) as amount, 
       DATE_FORMAT(o.order_datetime, '%d/%m/%Y') as date, SUM(OD.quantity) 
       as quantity, TIME(o.order_datetime) as time, MAX( status_id ) as laststatus
     FROM table_orders o
     INNER JOIN table_order_details AS OD ON o.order_id = OD.order_id
     INNER JOIN table_order_status s ON s.order_id = o.order_id
     GROUP BY o.order_id

The sum doesn't returns the current value because it get multiplied with each join.

You can solve this be getting this with a select instead of using just the columns.

(SELECT SUM(OD.product_sell_price * OD.quantity) FROM table_order_details as OD WHERE OD.order_id=o.order_id)  as amount

And for the quantity use:

(SELECT SUM(OD.quantity)  FROM table_order_details as OD WHERE OD.order_id=s.order_id) as quantity, 

So the final query will be:

SELECT oo.order_id, oo.amount, oo.date, oo.quantity, oo.time, value_desc
FROM 
(
       SELECT 
       s.order_id,  
       (SELECT SUM(OD.product_sell_price * OD.quantity) FROM table_order_details as OD WHERE OD.order_id=s.order_id) as amount, 
       DATE_FORMAT(o.order_datetime, '%d/%m/%Y') as date,
       (SELECT SUM(OD.quantity)  FROM table_order_details as OD WHERE OD.order_id=s.order_id) as quantity, 
       TIME(o.order_datetime) as time, MAX( status_id ) as laststatus
     FROM table_orders o
     INNER JOIN table_order_status s ON s.order_id = o.order_id
     GROUP BY o.order_id
)oo

INNER JOIN table_order_status_values ON value_id = laststatus

order by order_id DESC

And it will output:

1 200 12/12/2015 3 19:42:47 CONFIRIM

edit - i have also removed the

INNER JOIN table_order_details AS OD ON o.order_id = OD.order_id

No need for it to be joined in this query

Answer 2

Firstly your SQL is not valid, MySQL ignore this invlaid SQL but the result will not be what you expected.

SELECT o.order_id,
    oo.amt,
    DATE_FORMAT(o.order_datetime, '%d/%m/%Y') date,
    oo.qty, 
    TIME(o.order_datetime) time,
    osv.value_desc  
FROM table_orders o, table_order_status_values osv, (
    SELECT order_id, sum(quantity) qty, sum(product_sell_price*quantity) amt
    FROM table_order_details
    GROUP BY 1
) AS oo, (
    SELECT order_id, max(status_id) last_status
    FROM table_order_status
    GROUP BY 1
) AS os
WHERE o.order_id = os.order_id AND os.last_status = osv.status_id
AND o.order_id = oo.order_id