mysql calculating to get profit or loss from 2 tables ( Purchases and Sales) in FIFO mode

how to calculate profit or loss from the 2 tables?

create table items(id int primary key auto_increment, name varchar(255));

insert into items value(null,'A');
insert into items value(null,'B');
insert into items value(null,'C');
insert into items value(null,'D');

create table purchase(id int primary key auto_increment, item_id int,qnt int,price int);

insert into purchase value(null,1,10,20);
insert into purchase value(null,2,10,22);
insert into purchase value(null,3,10,25);
insert into purchase value(null,4,10,18);
insert into purchase value(null,5,10,25);

create table sales(id int primary key auto_increment, item_id int,qnt int,price int);
insert into purchase value(null,1,2,25);
insert into purchase value(null,2,3,15);
insert into purchase value(null,1,3,26);
insert into purchase value(null,1,2,22);

what I need is to put the cost of each item from the purchase table like this,(salespriceqnt)-( costpriceqnt) as profit/loss

no idea how to do it, right now I am doing this with PHP which takes too much time.

To directly answer your question as I understand it - this query should do the trick

select items.name as item, coalesce(sales_totals.total, 0) as total_sales, coalesce(purchases_totals.total, 0) as total_purchases, coalesce(sales_totals.total, 0) - coalesce(purchases_totals.total, 0) as profit
from items
left join
 (
    select item_id, sum(qnt*price) as total
    from purchase
    group by item_id
 ) purchases_totals on purchases_totals.item_id = items.id
left join
 (
    select item_id, sum(qnt*price) as total
    from sales
    group by item_id
 ) sales_totals on sales_totals.item_id = items.id;

This will yield the below output

Some things to note

• Since this uses left joins to the purchases and sales tables based on the items table, and there are no 'E' items in the items table, it will not show any results for that item even if there are purchases or sales
• I have used a coalesce function on the subquery results in the outer select to allow records with no purchases or sales to return 'cleanly', ie, 0 instead of null

This can easily be expanded to include other calculations, as demonstrated below

select items.name as item, coalesce(sales_totals.total, 0) as total_sales, coalesce(purchases_totals.total, 0) as total_purchases, coalesce(sales_totals.total, 0) - coalesce(purchases_totals.total, 0) as profit, coalesce(purchases_totals.bought, 0) - coalesce(sales_totals.sold, 0) as stock_on_hand
from items
left join
 (
    select item_id, sum(qnt*price) as total, sum(qnt) as bought
    from purchase
    group by item_id
 ) purchases_totals on purchases_totals.item_id = items.id
left join
 (
    select item_id, sum(qnt*price) as total, sum(qnt) as sold
    from sales
    group by item_id
 ) sales_totals on sales_totals.item_id = items.id;

This adds in a a sum of 'qnt' in each of the subqueries which can then be subtracted in the parent query to provide a stock level

I leave it as an exercise for the reader to further modify the query to calculate the value of the stock on hand, which to me would be the logical next addition