CODEWITHSUNDEEP
sql-servert-sql
David Brierton
David Brierton

Reputation: 7397

How to add positive and negative totals from the same column

I am using sql server with a table called dbo.over_short and I am trying to figure out how to add totals from the same column.

I have a column called FINAL_OVER_SHORT_AMOUNT it has design of decimal(10,2).

In this field there are positive numbers (ex 608.90) neutral numbers like (ex 0.00) and negative numbers like (ex -608.90).

Is it possible to add all the positive numbers together and all the negative numbers together (numbers starting with a minus) seperately.

To basically have a report of how much was over and how much was negative? (Without having the negatives subtract from the positive. Two separate totals)

select 
  ID,
  TRANSACTION_DATE,
  ASSOCIATE_ID,
  AGENCY_CODE,     
  FINAL_OVER_SHORT_AMOUNT
from
  dbo.over_short

Any help with this would be greatly appreciated!

Upvotes: 0

Views: 322

Answers (3)

Shwetali
Shwetali

Reputation: 31

You can use this
select sum(case when FINAL_OVER_SHORT_AMOUNT>0 then FINAL_OVER_SHORT_AMOUNT else 0 end)as Positive, sum(case when FINAL_OVER_SHORT_AMOUNT<0 then FINAL_OVER_SHORT_AMOUNT else 0 end) as Negative from over_short
or You can seperate store results in Temp table and Use it
select sum(case when FINAL_OVER_SHORT_AMOUNT>0 then FINAL_OVER_SHORT_AMOUNT else 0 end)as Positive, sum(case when FINAL_OVER_SHORT_AMOUNT<0 then FINAL_OVER_SHORT_AMOUNT else 0 end) as Negative into #Temp from [dbo].[Temp]

select sum(Positive) as PositiveSum, sum(Negative) as NegativeSum from #Temp

Upvotes: 1

HABO
HABO

Reputation: 15816

Sign is a handy little function:

-- Sample data.
declare @Samples as Table ( Sample Decimal(10,2) );
insert into @Samples ( Sample ) values
  ( -10.00 ), ( -5.00 ), ( 0.00 ), ( 42.00 ), ( 8.00 );

-- Demonstrate separating values by sign.
select Sample,
  Sample * -Sign( Sign( Sample ) - 1 ) as Negative,
  Sample * Sign( Sign( Sample ) + 1 ) as Positive
  from @Samples;

-- Calculate the sums.
select Sum( Sample ) as Total,
  Sum( Sample * -Sign( Sign( Sample ) - 1 ) ) as NegativeTotal,
  Sum( Sample * Sign( Sign( Sample ) + 1 ) ) as PositiveTotal
  from @Samples;

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

You may use conditional aggregation here:

SELECT
    SUM(CASE WHEN FINAL_OVER_SHORT_AMOUNT >= 0
             THEN FINAL_OVER_SHORT_AMOUNT ELSE 0 END) AS pos_amount,
    SUM(CASE WHEN FINAL_OVER_SHORT_AMOUNT < 0
             THEN FINAL_OVER_SHORT_AMOUNT ELSE 0 END) AS neg_amount
FROM dbo.over_short;

You could also use a similar query along with GROUP BY, assuming you wanted the positive/negative sums for a given group/column, e.g. the ID column.

Upvotes: 1

Related Questions