How to subtract from one row column to another row column in same table

Question

1.00/5 (1 vote)

See more:

I want subtract buyamount from contractno 001 with saleamount from contractno 002 to get amount revenue

Below is my data

REFNO | DEALMASTERKEY | CONTRACTNO | BUYAMOUNT | SALEAMOUNT | RATE | REVENUE
==========================================================
2012110 | 123 | 001 | 1,500,000 | 1,000,000 | 1.5 |
2012110 | 456 | 002 | 1,000,000 | 1,700,000 | 1.7 |

What I have tried:

SQL

SELECT 
dd.REFNO, dd.DEALMASTERKEY, dd.CONTRACTNO, dd.BUYAMOUNT, dd.SALEAMOUNT, dd.RATE
,REVENUE = (SELECT ABS(dl.BUYAMOUNT) from DF_DEALMASTER dl where dl.CONTRACTNO = '001' and dl.DEALMASTERKEY = dd.DEALMASTERKEY) - 
(SELECT ABS(dl.SALEAMOUNT) from DF_DEALMASTER dl where dl.CONTRACTNO = '002' and dl.DEALMASTERKEY = dd.DEALMASTERKEY)

FROM DF_DEALMASTER dd

Posted 3-Mar-20 5:25am

Amira Najihah

Updated 3-Mar-20 15:34pm

v6

Add a Solution

Comments

Richard Deeming 3-Mar-20 11:45am

The data you've shown has no connection between the two rows.

The query you've tried references columns which aren't in the sample data.

There's no suggestion of how you're identifying which row to subtract from which other row.

Amira Najihah 3-Mar-20 21:22pm

contractno 001 is first row
contractno 002 is second row

The query I had tried to get amount revenue.
Revenue = (dl.buyamount where contractno = 001) - (saleamount where contractno = 002)

revenue = 1,500,00 - 1,700,000 (This is how I try to subtract)

Amira Najihah 3-Mar-20 21:32pm

Sorry I forgot to put reference no that tie two rows

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2020-03-03T06:07:00

See the comments from @RichardDeeming. Your first set of errors are

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'KEY'.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'FROM'.

The first one is caused because KEY is a keyword. When using keywords as column names (avoid if possible) surround the column name in square brackets i.e. [KEY]

After sorting that out you get several errors like

Msg 207, Level 16, State 1, Line 8
Invalid column name 'DEALMASTERKEY'.

- You have not shared the full table schema with us. If I tidy that up I get

SQL

declare @DF_DEALMASTER table ([KEY] int,CONTRACTNO varchar(3), BUYAMOUNT int, SALEAMOUNT int, RATE decimal(15,2))
insert into @DF_DEALMASTER ([KEY],CONTRACTNO, BUYAMOUNT, SALEAMOUNT, RATE) values
(123,'001',1500000, 1000000,  1.5 ),
(456,'002',1000000, 1700000,  1.7 )

SELECT 
dd.[KEY], dd.CONTRACTNO, dd.BUYAMOUNT, dd.SALEAMOUNT, dd.RATE
,REVENUE = (SELECT ABS(dl.BUYAMOUNT) from @DF_DEALMASTER dl where dl.CONTRACTNO = '001') - 
(SELECT ABS(dl.SALEAMOUNT) from @DF_DEALMASTER dl where dl.CONTRACTNO = '002' )

FROM @DF_DEALMASTER dd

And that returns the results

KEY	CONTRACTNO	BUYAMOUNT	SALEAMOUNT	RATE	REVENUE
123	001	1500000	1000000	1.50	-200000
456	002	1000000	1700000	1.70	-200000

which looks to be correct.

Of course this will only work if you always have contracts '001' and '002' for any given value of DEALMASTERKEY

What I suspect you really want is to subtract values from the previous row - in which case you can use the LAG and LEAD functions of SQL Server ... SQL Server Window Functions LEAD and LAG[^]

Gerry Schmitz · Answer 2 · 2020-03-03T06:23:00

Solution 3

I think you have an answer (200,000), looking for a solution.

Perhaps you just want to SUM the BUYAMOUNT and subtract that from the "SUM of the SALESAMOUNT".

Same answer: 200,000.

Posted 3-Mar-20 6:23am

Gerry Schmitz

OriginalGriff · Answer 3 · 2020-03-03T06:01:00

In order to do that, you need to establish some rules, which relate data in one row to data in the other.

If there is a "sortable" field that you can arrange with ORDER BY then you can use LEAD and LAG to reference values in the next or previous row: LEAD (Transact-SQL) - SQL Server | Microsoft Docs[^] LAG (Transact-SQL) - SQL Server | Microsoft Docs[^]

If there is a column which can be used to "tie" two related rows together, you can texh "matching" data using JOIN: SQL Joins[^]

But it's not at all obvious from the trivial data sample you show what could establish the critical relationship - and as Richard has pointed out, your code sample references columns that don't exist ...

How to subtract from one row column to another row column in same table

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0