Create a query to select data from MYSQL WHERE min(i_status) = 4

I Have two MY SQL tables OrderTable

+----------+--------+
| o_number | o_name |
+----------+--------+
|        1 | toys   |
|        2 | bags   |
|        3 | Laptop |
+----------+--------+

and itemTable

+----+----------+--------+-------+--------+----------+
| id | o_number | i_name | i_pcs | i_cost | i_status |
+----+----------+--------+-------+--------+----------+
|  1 |        1 | item1  |     1 |      2 |        1 |
|  2 |        1 | item2  |     1 |      3 |        1 |
|  3 |        2 | item3  |     1 |      4 |        1 |
|  4 |        2 | item4  |     1 |      5 |        2 |
|  5 |        3 | item5  |     1 |      6 |        4 |
|  6 |        3 | item6  |     1 |      7 |        6 |
+----+----------+--------+-------+--------+----------+

Im recive all orde whit query1 -ResultTable1 – it ok

query1
     SELECT OrderTable *, sum(i_cost*i_pcs), min(i_status)
    LEFT JOIN ItemTable ON ItemTable.o_number = OrderTable.o_number
    GROUP BY OrderTable.o_number ORDER BY OrderTable.o_number DESC

ResultTable1

+----------+--------+------------+------------+
| o_number | o_name | total_cost | min_status |
+----------+--------+------------+------------+
|        1 | toys   |          5 |          1 |
|        2 | bags   |          9 |          1 |
|        3 | Laptop |         13 |          4 |
+----------+--------+------------+------------+

but I need to receive all orders, goods with which have a minimum status of 4, and the sum of all goods from this order, not only goods with ID = 4 My query should look like this, but it does not give a result.

query2
 SELECT OrderTable *, sum(i_cost*i_pcs), min(i_status)
LEFT JOIN ItemTable ON ItemTable.o_number = OrderTable.o_number
WHERE min(i_status) = 4
GROUP BY OrderTable.o_number ORDER BY OrderTable.o_number DESC

I want to see next result:

+----------+--------+------------+------------+
| o_number | o_name | total_cost | min_status |
+----------+--------+------------+------------+
|        3 | Laptop |         13 |          4 |
+----------+--------+------------+------------+

if I change the request where to WHERE i_status = 4 without MIN I will get almost what I need, but the order amount will be less, since not all goods are counted.

+----------+--------+------------+------------+
| o_number | o_name | total_cost | min_status |
+----------+--------+------------+------------+
|        3 | Laptop |          6 |          4 |
+----------+--------+------------+------------+

Answer

Thank you for taking the time to resolve my issue. But I need to comply with two conditions at the same time WHERE min(i_status) = 4 AND sum(i_cost*i_pcs) whit all item status Perhaps the only answer is array processing without WHERE

Advertisement

Answer