The value of the order and order amount is not set correctly 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 the order ID, order amount, order date, order quantity, order time, desc_value.

I fulfill this request.

 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 Conclusion:

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

But the expected result:

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

I get the order quantity and the number of orders twice (or three times) depending on the number of statuses for this order.

How to fix it? Any help would be greatly appreciated.

Answer 1

The problem 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 does not return the current value, because it is multiplied by each connection.

You can solve this problem with select instead of using only 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 to use the quantity:

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

Thus, the final request will look like this:

 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 he will output:

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

change - I also deleted

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

No need to bind it in this query

Answer 2

First, your SQL is invalid, MySQL ignores 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 

Answer 3

You must first focus the first subheading on getting all the order information except this state, and then join the status information.

It might look like this:

 SELECT order_with_status.order_id AS order_id, order_with_status.amount AS amount, order_with_status.`date` AS `date`, order_with_status.quantity AS quantity, order_with_status.`time` AS `time`, tosv.value_description AS current_status FROM ( /* Add maximum order status value to result set */ SELECT order.order_id AS order_id, order.amount AS amount, order.`date` AS `date`, order.quantity AS quantity, order.`time` AS `time`, MAX(tos.status) AS current_status_id FROM ( /* Get rolled-up order information as start to result set */ SELECT o.order_id AS 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` FROM table_orders AS o INNER JOIN table_order_details AS od ON o.order_id = od.order_id GROUP BY o.order_id ) as order INNER JOIN table_order_status AS tos ON order.order_id = tos.order_id ) as order_with_status INNER JOIN table_order_status_values AS tosv ON order_with_status.current_status_id = tosv.value_id ORDER BY order_with_status.order_id DESC 

Note that the schema itself makes this very complex request, which probably will not work very well if you need to regularly execute this request on your system. If you made a simple change to move the order status identifier field to the order table, you can greatly simplify it (turning off the table_order_status table to simply audit the history of state changes that are not used at all in this type of request). This makes sense that there seems to be no need to work with a history of order changes when you try to just get the current order data.

In this case, the query may be as simple as

 SELECT o.order_id AS order_id, tosv.value_description AS current_status, 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` FROM table_orders AS o INNER JOIN table_order_status_values AS tosv ON o.order_status_id = tosv.value_id INNER JOIN table_order_details AS od ON o.order_id = od.order_id GROUP BY o.order_id ORDER BY o.order_id DESC 

This can be simplified if you want to put the state description in the order table as opposed to the identifier (this will eliminate the additional table join to get the description).

Now, whether this obvious de-normalization will make sense for your application will be largely determined by the use cases with the application. If, for example, you do not gain access to audit data a lot or only need to present this data in the context of the same order, this may make sense.

Answer 4

The subquery that you used without the by and sum / max groups:

 SELECT s.order_id, OD.product_sell_price * OD.quantity as amount, DATE_FORMAT(o.order_datetime, '%d/%m/%Y') as date, OD.quantity as quantity, TIME(o.order_datetime) as time, 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 

would lead to:

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

Adding SUM / MAX and the group, it gives the result that you asked in the question:

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

Which is correct according to the request you wrote. The solution is that you should get MAX (status_id) separately. Therefore, use the following 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(select MAX( status_id ) from table_order_status ts where ts.order_id = o.order_id group by ts.order_id) as laststatus FROM table_orders o INNER JOIN table_order_details AS OD ON o.order_id = OD.order_id GROUP BY o.order_id )oo INNER JOIN table_order_status_values ON value_id = laststatus order by order_id DESC 

It will use the lines:

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

Providing the expected result:

 1 200 2015:12:12 319:42:47 2