nilan59 nilan59 - 11 months ago 58
MySQL Question

SQL operations in same table

I have a table with columns called:

  1. ID (int) (not unique)

  2. Type (varchar)

  3. Amount (decimal)

For every id there are two 'Type's of records. Called charge and discount with two different amounts.
I want to deduct the discount amount from charge amount and get the final amount for every id.
As an example for Id '2' i may have a charge amount of 200 and a discount amount of 30 so I want my result to be 170 for Id '2'

I can't figure out a clean way to do this.

Answer Source

If you can be absolutely sure there are no other variants of type other than 'charge' and 'discount', then this query should work:

select ID, 
      when Type = 'charge' then Amount 
      when Type = 'discount' then Amount*-1
    end) as final_amount
from my_table
group by ID

It works by checking first what the type is before deciding what to contribute to the sum. If the value is a 'charge' then the amount is used, otherwise if the value is a 'discount' then the amount is negated and will be deducted from the sum.


  1. 'charge' and discount are the only values possible for type
  2. Amount is always positive irregardless of type