CODEWITHSUNDEEP
mysql
timw07
timw07

Reputation: 339

MySQL query to treat a column as positive or negative depending on another value

I have a table in MySQL structured the following way:

table1
------
userid  varchar(20)
type    varchar(20)
amount  integer

Where type can be deposit or withdrawal.

I want to perform a query on this table where I can get the net balance of sum of withdrawals - deposits for a given user.

Is this possible with MySQL or do I need to run two separate queries and do the subtraction in code?

Upvotes: 0

Views: 1068

Answers (3)

AAzami
AAzami

Reputation: 406

You could perform something like this. Adjust table name and your type values. It will group per user total Deposit and Debit and give you a balance column.

SELECT userid, 
   Sum(CASE 
         WHEN [type] = 'Deposit' THEN amount 
         ELSE 0 
       END) AS deposit, 
   Sum(CASE 
         WHEN [type] = 'Debit' THEN amount 
         ELSE 0 
       END) AS debit, 
   Sum(CASE 
         WHEN [type] = 'Deposit' THEN amount 
         ELSE amount * -1 
       END) AS Balance 
   FROM   tblaccount 
GROUP  BY userid

Upvotes: 1

Eric Leschinski
Eric Leschinski

Reputation: 153872

Subqueries should be able to do this:

select 
   (select sum(amount) 
    from table1 
    where type='deposit' 
    and userid = 'user1'
   ) - 
   (select sum(amount) 
    from table1 
    where type='withdrawal' 
    and userid = 'user1') 
 as deposit_minus_withdrawls

or with a case statement.

SELECT userid, SUM( 
  CASE TYPE WHEN  'withdrawal'
  THEN amount
  ELSE 0 
  END ) - 
  SUM( 
  CASE TYPE WHEN  'deposit'
  THEN amount
  ELSE 0 
  END ) AS balance
FROM table1
GROUP BY userid
LIMIT 0 , 30

Upvotes: 0

grape_mao
grape_mao

Reputation: 1153

how about this:

select sum(
          case type
          when 'deposit' then amount
          when 'withdrawal' then -amount
          end 
          ) as balance
from $table where userid = $id

Upvotes: 3

Related Questions