CODEWITHSUNDEEP
mysqlsqlcodeigniterphpmyadmin
Disputed
Disputed

Reputation: 80

Sum of two columns by id to one field

i need to make sum of two columns by id to one field by that has the same id

for example : i want the Balance which is income - expenses = balance

Table Transactions:

=========================================================
|  id  | idBudget  |  expenses      ||      income      |
=====+========+===============+=====|=====+========+====|
|   1  |      2    |     10         ||        0         |  
|----+--------+---------------+-----||----+--------+----|
|   2  |      3    |     200        ||        0         |              
|----+--------+---------------+-----||----+--------+----|
|   3  |      2    |     1          ||     100          |  
|----+--------+---------------+-----||----+--------+----|
|   4  |      2    |     0          ||     1000         |              
|----+--------+---------------+-----||----+--------+----|

Table Budget:

=====================================
|  idBudget        |  Balance       |
=====+========+===============+=====|
|     2            |     1090       |
|----+--------+---------------+-----|
|     3            |     -200       |
|----+--------+---------------+-----|

i tired to use Triggers but i think i don't know how to implement it

Upvotes: 0

Views: 193

Answers (2)

SQB
SQB

Reputation: 4078

CREATE TABLE starting_balance AS (SELECT * FROM budget);

DROP TABLE budget;

CREATE VIEW budget AS (
    SELECT
        sb.idBudget,
        sb.balance + SUM(t.income - t.expenses) AS balance
    FROM starting_balance sb
    LEFT JOIN transactions t ON (t.idBudget = sb.idBudget)
    GROUP BY
        sb.idBudget,
        sb.balance
);

In other words, use a view instead of a table. You can update the starting balance from time to time (and either delete or flag the transactions you no longer need!), and you could use a materialized view.

Upvotes: 1

Mihai
Mihai

Reputation: 26784

Something like this? 

INSERT INTO Budget(SELECT idBudget, SUM(income)-SUM(expenses) as Balance
FROM Transactions GROUP BY idBudget )

delimiter //
CREATE TRIGGER balance AFTER INSERT  ON Transactions
FOR EACH ROW
BEGIN
INSERT INTO Budget(SELECT NEW.idBudget, SUM(NEW.income)-SUM(NEW.expenses) as Balance
    FROM Transactions GROUP BY idBudget )
END;
delimiter ;

Upvotes: 0

Related Questions