'Subtraction in same table in same row with where condition #SQL

Using a SQL query to subtract in same table:

Table1

grade   record  total_amount
------------------------------
DSP     receipt     17258
DSP     sales       16313
OPC43   receipt     95442
OPC43   sales       92856
OPC53   receipt    371752
OPC53   sales      368985
PPC     receipt    156023
PPC     sales      152803
vajram  receipt     36100
vajram  sales       36100

Answer needed

 945    DSP
2586    OPC43
2767    OPC53
3220    PPC
   0    vajram
sql


Solution 1:[1]

Is there ever only two entries per grade? Always just a receipt and sales? If that's the case, then something like the following would do the trick

SELECT receipt.total_amount - sales.total_amount, grade 
FROM 
   (SELECT * FROM yourtable WHERE record = 'receipt') receipt
   INNER JOIN (SELECT * FROM yourtable WHERE record = 'sales') sales
       ON receipt.grade = sales.grade

Solution 2:[2]

I'd try something like that:

select grade, sum(case when record = 'receipt' then total_amount else 0-total_amount end)
from table
group by grade

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 JNevill
Solution 2 IÅ‚ya Bursov

Related Questions