Subtraction in same table in same row with where condition #SQL-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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