MySQL Sum + Inner Query in same 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 |
+----+---------+----------+---------+-------+

Purchased asset is FromType 'Bank' to ToType 'Asset'.
And Assets Sold is vise visa.

How can I display the table like the one shown below.

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

Thanks in advance.

I had tried this query. But it is not working 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

@RandomSeed pls check the question. I had updated it – Eugine Joseph Aug 30 '13 at 13:30 — Eugine Joseph, Aug 30 '13 at 13:30

score 4 · Accepted Answer · answered Aug 30 '13 at 13:55

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

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 inner query, first we sum up the amounts where fromtype is bank, otherwise 0, and the same thing the other way around for fromtype = asset. Whole thing gets grouped by assetid, of course.

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 aliased column names are not available directly there). Voilà!

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

Yes, the last one is -5000. I am sorry for that mistake. :) – Eugine Joseph Aug 30 '13 at 14:37 — Eugine Joseph, Aug 30 '13 at 14:37

score 1 · Answer 2 · answered Aug 30 '13 at 14:02

I assume you meant purchase - sale = balance , and you wanted -5000 in the last row

    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;

MySQL Sum + Inner Query in same table

2 Answers2