nilan59 nilan59 - 16 days ago 5
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

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, 
sum(case 
      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.

Assumptions:

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