In MySQL I have the following two tables: Table 1 has all the orders and Table 2 the productions. I need a query that sums table2 R-001 amounts and subtract from it Table 1 close amounts.

Table 1

Table 2

Result looking for:

How can I get this result?

CodePudding user response：

Your desired results are somewhat confusing since you would not have the value of a row as a column alias, but perhaps this is what you are after:

select t2.barcode, 
    Sum(t2.amount) - coalesce((
        select Sum(t1.amount) 
        from t1
        where t1.barcode=t2.barcode and t1.state='close'
    ),0) as Amount
from t2  
where t2.barcode='R-001'
group by t2.barcode

CodePudding user response：

You need a join based on subqiery for amount of table 2 and table 1 group by barcode

select t1.barcode,  t2.t2_amount - sum(t2.amount )
from table1 t1
inner join  (
    barcode, sum(amount ) t2_amount
    from table1
    group by barcode
) t2 on t1.barcode = t2.barcode
where t1.state ='close'
group by t1.barcode,  t2.t2_amount