How to Subtract values in a same column....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to Subtract values in a same column....

Author

Topic

rkumar28
Starting Member

49 Posts

Posted - 2005-09-28 : 13:24:02

Hi,
I am new to sql server. I have a situation where I have to subtract the values from one column. There is a function "SUM" to do the Addition. Is there a function to do the subtraction.

e.g.: I have a field called COL1....and I have to subtract the values in this column grouping it by MONTH.

I will really appreciate any advice in this regards.

Thanks

Raj

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-28 : 13:27:29

Subtract them from what?

Please following the suggestions in the link below

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rkumar28
Starting Member

49 Posts

Posted - 2005-09-28 : 13:45:46

I want to subtract them from the values of same column.
Let say there is a table A. It has two columns called Month(date) and Pol1(Integer).
These are the records in the table...

Month Pol1
January 10000
January 30000
February 10000
February 20000

There can be many records in a month. I have shown only two records above.

I am trying to subtract the values of column Pol1 grouping it by Month.

I want to get the result something like:

January 20000 .....(I got this by subtrating 30000 with 10000 for January month)
February 10000 .....(I got this by subtrating 20000 with 10000 for January month)

Is there a funtion to subtract like we have Sum function.

Thanks

Raj

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-28 : 13:51:15

What if there are three or more records?

Can you explain why you want to do this?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-09-28 : 13:56:01

Remember that A-B is equal to A + (-B) which is equal to A + (-1 * B) which is equal to the sum of A and -1*B ....

Does that clue help ?

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-28 : 14:06:54

No

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rkumar28
Starting Member

49 Posts

Posted - 2005-09-28 : 14:30:18

Hi,
I am trying to generate a report for the end users. I have a Insurance policies and these policies has Annutization Amount. These amounts varies for each policies in a month.
E.g.: There is a policy 123456 and its anutization amount changes couple of times in month.....Below I have given a sample for the month of January.....for one policy

DATE…………….POLICY…….AMOUNT
01-02-2005………123456………..200
01-05-2005………123456……….. 50 The amount changes
01-15-2005………123456………..200 The amount changes again
01-20-2005……….123456……….300 The amount changes again

Basically end user wants to see the result of 300 -200 -50 -200= -150 for the month of January. The subtraction has to be done from the latest date to an earlier date for a particular policy for a month.

My problem is I am not able to figure out how to do the subtraction within one column.

Thanks

Raj

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-09-28 : 14:41:03

quote:
Originally posted by jsmith8858

Remember that A-B is equal to A + (-B) which is equal to A + (-1 * B) which is equal to the sum of A and -1*B ....

Does that clue help ?

In other words, some values need to make negative, sum left positive, and then simply use SUM() ....

In your case, leave the value with the highest date per policy per month positive, multiply the rest by -1, and then add them all up:


SELECT 
   Policy, Year(Date), Month(Date), 
   SUM(case when Date= LastDate then 1 else -1 end * Amount) as Amount
FROM
(
  SELECT Policy, 
     Date, 
     (select 
        Max(Date) from YourTable B
      where 
        A.Policy = B.Policy and
        Year(A.Date) = Year(B.Date) and
        Month(A.Date) = Month(A.Date)
     ) as LastDate, 
     Amount
  FROM 
     YourTable A
) tmp
GROUP BY Policy, Year(Date), Month(Date)

rkumar28
Starting Member

49 Posts

Posted - 2005-09-28 : 17:37:09

Thanks a bunch. This logic worked....

Raj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources