MySQL Sum + Internal query in one table

Question

This is my MySQL table layout.

+----+---------+----------+---------+-------+ | id | AssetId | FromType | ToType | Amount | +----+---------+----------+---------+-------+ | 1 | 1 | Bank | Asset | 10000 | +----+---------+----------+---------+-------+ | 2 | 2 | Bank | Asset | 5000 | +----+---------+----------+---------+-------+ | 3 | 2 | Asset | Bank | 4000 | +----+---------+----------+---------+-------+ | 4 | 3 | Asset | Bank | 3000 | +----+---------+----------+---------+-------+ | 5 | 3 | Asset | Bank | 2000 | +----+---------+----------+---------+-------+ 

The acquired asset is FromType 'Bank' to ToType 'Asset'.
And the assets sold are a visa visa.

How to display a table as shown below.

 +---------+----------+-----------+---------+ | AssetId | Purchase | Sale | Balance | +---------+----------+-----------+---------+ | 1 | 10000 | 0 | 10000 | +---------+----------+-----------+---------+ | 2 | 5000 | 4000 | 1000 | +---------+----------+-----------+---------+ | 3 | 0 | 5000 | 5000 | +---------+----------+-----------+---------+ 

Thanks in advance.

I tried this request. But it does not work properly

 SELECT id as AssetId, debit, credit, 'Asset' AS tb_name FROM ( ( SELECT id, SUM( `Amount`) AS debit, '0' AS credit FROM `erp_assets` WHERE FromType = 'Asset' GROUP BY AssetId ) UNION ALL ( SELECT id, SUM( `Amount` ) AS credit, '0' AS debit FROM `erp_assets` WHERE ToType = 'Asset' GROUP BY AssetId ) ) AS tb1 

Answer 1

I assume that in the last line of your example, the output balance should be -5000, not 5000, right?

 SELECT *, purchase - sale AS balance FROM ( SELECT assetid, sum(if(fromtype='bank', amount, 0)) AS purchase, sum(if(fromtype='asset', amount, 0)) AS sale FROM foo f1 GROUP BY assetid ) f2 

In the internal query, we first sum up the amounts in which fromtype is a bank, otherwise 0, and the same, on the contrary, for fromtype = asset. Of course, the whole thing is grouped by asset.

And then in the outer query we select everything from the inner query and build the difference (which is not directly possible in the inner query, because the column alias names are not available directly there). Voila!

See here: http://sqlfiddle.com/#!2/05652/2

Answer 2

I assume you meant buy-sale = balance, and you wanted -5000 in the last line

  CREATE TABLE bla ( id int AUTO_INCREMENT, AssetId int, FromType varchar(255), ToType varchar(255), Ammount int, PRIMARY KEY(id) ) ENGINE = MyISAM; INSERT INTO bla(AssetId,FromType,ToType,Ammount) VALUES (1,'Bank','Asset',10000), (2,'Bank','Asset',5000), (2,'Asset','Bank',4000), (3,'Asset','Bank',3000), (3,'Asset','Bank',2000); SELECT a.AssetId,a.Purchase as Purchase,a.Sale, a.Purchase-a.Sale as Balance FROM ( SELECT a.AssetId, (SELECT IFNULL(SUM(b.Ammount),0) FROM bla as b WHERE b.AssetId=a.AssetId AND FromType='Bank' AND ToType='Asset') as Purchase, (SELECT IFNULL(SUM(b.Ammount),0) FROM bla as b WHERE b.AssetId=a.AssetId AND FromType='Asset' AND ToType='Bank') as Sale FROM bla as a Group By a.AssetId) as a;