pape pape - 3 months ago 14
SQL Question

SQL - subtraction row

I have query

select VALUE,
Source1,
Source2,
DocNo
from myTable


enter image description here

I want this

for DocNo A1
if(Source1=1 and Source2=0) VALUE 34
if(Source1=1 and Source2=0) VALUE 21
subtraction two row 34 - 21 = 13


and get this

Any idea?

enter image description here

Answer

If I understand your issue correctly, the query below is you want:

SELECT Value, Score1, Score2, DocNo
FROM TestTable 
WHERE Score1 = 0

UNION 

SELECT MAX(Value) - MIN(Value) AS Value, 1, 1, DocNo
FROM TestTable 
WHERE Score1 = 1
GROUP BY DocNo
ORDER BY DocNo, Score1

DEMO for the same.