SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Subtracting One Row From Another Row		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

goodyear
Starting Member

2 Posts
Posted - 07/04/2012 :  15:45:33  Show Profile  Reply with Quote
Hi Everyone,

I need help on how to subtract a value from a row on another row.

For example:

I have a simple table called "COST."

In my COST table I need to subtract the cost of Calif. - SoCal, so it would (2,000-500=1500).


--TABLE COST---

Location COST
ROW 1 Calif. 2,000
ROW 2 SoCAL 500


On my final output what I would ultimately want is to have the following.

Location COST
ROW 1 Calif. 2,000
ROW 2 SoCAL 500
Row 3 Calif_2 1,500


Is this possible? Thanks in advance!

My

visakh16
Very Important crosS Applying yaK Herder

India
47040 Posts
Posted - 07/04/2012 :  16:54:10  Show Profile  Reply with Quote
yep possible

SELECT *,t1.Cost AS PrevCost
FROM table t
OUTER APPLY (SELECT SUM(CASE WHEN Location LIKE 'Calif%' THEN COST ELSE -1 * COST END) AS Cost
             FROM Table
             WHERE PK < t.PK
            )t1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

goodyear
Starting Member

2 Posts
Posted - 07/04/2012 :  20:44:03  Show Profile  Reply with Quote
Thanks Visakh for responding.

I tried your suggestion, but and it error out. I'm not sure if it's my syntax or that OUTER APPLY is not recognized in Oracle.

CREATE TABLE GY_COST AS
SELECT *,t1.cost AS PrevCost
FROM table t
OUTER JOIN (SELECT SUM(CASE WHEN LOCATION LIKE 'CALIF%' THEN COST ELSE -1 * COST END) AS COST
FROM TABLE
WHERE ROWNUM<t.ROWNUM) t1;
Edited by - goodyear on 07/04/2012 20:48:18
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47040 Posts
Posted - 07/04/2012 :  23:37:16  Show Profile  Reply with Quote
you're in wrong forum then!
This is MS SQL Server forum so please post in Oracle forums like www.orafaq.com for Oracle related help.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000