SUM of a Column

1

Hi,

I need to calculate the SUM of the column MUTATIE from a table but the PLUS or MINUS comes from another column, see picture.

The out of the SUM(MUTATIE) should be 440

is there a way to do this?

3

2

It can be something like this:

DECLARE @Mutatie AS TABLE
(
	Mutatie DECIMAL(9,4) NULL,
	PLUS_MIN CHAR(1) NULL
)

INSERT INTO @Mutatie
SELECT 200,'-'
UNION
SELECT 200.50,'+'

SELECT SUM(Mutatie * CASE WHEN ISNULL(PLUS_MIN,'+')='+' THEN 1 ELSE -1 END)
FROM @Mutatie
3

hi

hope this helps

create data script

drop table data
create table data (klant int, volgnummmer int , mutatie int, plus_minus varchar(1))

insert into data select 100044,4897 , 200 , '+'
insert into data select 100044,10494, 200 , '+'
insert into data select 100044,43814, 400 , '-'
insert into data select 100044,1451035, 200 , '+'
insert into data select 100044,297550, 40 , '+'
insert into data select 100044,314667, 200 , '+'

select 
  sum(cast(plus_minus + cast(mutatie as varchar) as int )) 
from 
  data

image
image702×360 52.8 KB

4

another way to do this


select 
   sum(concat(plus_minus,mutatie)*1) 
from 
  data
5

Thanx! that works!

6

Hi Rogier,

Thanx for the reply but i dont exactly know how to implement this in my query

7 
SELECT KLANT
, SUM(CASE WHEN plus_minus = '+' THEN mutatie END) - SUM(CASE WHEN plus_minus = '-' THEN mutatie END) AS SUM_MUTATIE
FROM tablename
GROUP BY KLANT