I Have two MY SQL tables OrderTable
x
+----------+--------+
| 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 |
+----------+--------+------------+------------+
Advertisement
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