Hello I am trying to make a WHERE clause where the condition is the id of the previous selection, example:
,(SELECT SUM(P1.amount) FROM table_s P1 WHERE P1.status = 4 AND P1.caseid = 20)
FROM table_s P1 GROUP BY P1.caseid";
P1.status = 4 AND P1.caseid = 20
WHERE P1.caseid = 20
WHERE P1.caseid = 45
WHERE P1.caseid = 35
id | caseid | amount | status
-- | ------------------------
1 | 20 | 10 | 4
2 | 45 | 10 | 4
3 | 20 | 10 | 4
1 ROW = caseid: 20 amount: 20 status 4
2 ROW = caseid: 45 amount: 10 status 4
$variable = 20
$variable = 10
I think I've worked out what you're asking...
The important note here is to use different aliases for your table in the outer and inner queries. Otherwise you have a serious scope problem. (If two instances of the same entity have the same name, how can MySQL ever know which one you're referring to? It will choose the one in the nearest scope. So, instead, call one of them, for example,
SELECT P1.*, ( SELECT SUM(lookup.amount) FROM table_s lookup WHERE lookup.status = 4 AND lookup.caseid = P1.caseid ) correlated_sub_query_total_by_caseid FROM table_s P1 GROUP BY P1.caseid
That said, you added another comment that seems to contract your question...
the idea is to select the sum of the amount for each caseid and display it. as caseid - sum
For that you just need an aggregation...
SELECT caseid, SUM(amount) FROM table_s GROUP BY caseid