CODEWITHSUNDEEP
mysqlsql
Gaddiel Sadoc Peralta
Gaddiel Sadoc Peralta

Reputation: 331

Get the balance of my users in the same table

Help please, I have a table like this:

 | ID | userId   | amount  | type    |
 -------------------------------------
 |  1 |       10 |  10     | expense |
 |  2 |       10 |  22     | income  |
 |  3 |        3 |  25     | expense |
 |  4 |        3 |  40     | expense |
 |  5 |        3 |  63     | income  |

I'm looking for a way to use one query and retrive the balance of each user.

The hard part comes when the amounts has to be added on expenses and substracted on incomes.

This would be the result table:

 | userId | balance |
 --------------------
 |   10   |  12     |
 |    3   |  -2     |

Upvotes: 1

Views: 539

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270623

This is easiest to do with a single group by:

select user_id,
       sum(case when type = 'income' then amount else - amount end) as balance
from t
group by user_id

Upvotes: 1

John Woo
John Woo

Reputation: 263843

You need to get each totals of income and expense using subquery then later on join them so you can subtract expense from income

SELECT  a.UserID,
        (b.totalIncome - a.totalExpense) `balance`
FROM
(
    SELECT  userID, SUM(amount) totalExpense
    FROM    myTable
    WHERE   type = 'expense'
    GROUP BY userID
) a INNER JOIN
(
    SELECT  userID, SUM(amount) totalIncome
    FROM    myTable
    WHERE   type = 'income'
    GROUP BY userID
) b on a.userID = b.userid

SQLFiddle Demo

Upvotes: 4

Scott Hunter
Scott Hunter

Reputation: 49893

You could have 2 sub-queries, each grouped by id: one sums the incomes, the other the expenses. Then you could join these together, so that each row had an id, the sum of the expenses and the sum of the income(s), from which you can easily compute the balance.

Upvotes: 0

Related Questions