2

In SQL Server you can do sum(field1) to get the sum of all fields that match the groupby clause.
But I need to have the fields subtracted, not summed.
Is there something like subtract(field1) that I can use in stead of sum(field1) ?

For example, table1 has this content : 

name field1  
A 1  
A 2  
B 4

Query:

select name, sum(field1), subtract(field1)  
from table1  
group by name

would give me:

A 3 -1  
B 4 4

I hope my question is clear.

EDIT :

there is also a sortfield that i can use. This makes sure that values 1 and 2 will always lead to -1 an not to 1.

What I need is all values for A subtracted, in my example 1 - 2 = -1

EDIT2 : if the A-group has values 1, 2, 3, 4 the result must be 1 - 2 - 3 - 4 = -8

GuidoG
  • 11,359
  • 6
  • 44
  • 79
  • 5
    subtract would translate to `SUM(-field1)` but from the example you gave that wouldn't give you the results you want. You want `+X1-X2-X3` which is not the reverse of sum, being `+X1+X2+X3`. – TT. Apr 11 '16 at 09:54
  • 8
    You would have to have a column that indicates an order because 2-1 is not 1-2 – juergen d Apr 11 '16 at 09:56
  • You must elaborate on how this function should work. What if the A-group had the following values: 1, 2, 3, 4, what should the end result be, and why? – Lasse V. Karlsen Apr 11 '16 at 10:28
  • if A group has values 1, 2, 3, 4 the result must be 1 - 2 - 3 - 4 = -8 – GuidoG Apr 11 '16 at 11:08

4 Answers4

3

Assuming that you have some column to indicate order, to select first element per group, you could use windowed functions to calculate your substract:

CREATE TABLE tab(ID INT IDENTITY(1,1) PRIMARY KEY, name CHAR(1), field1 INT);
INSERT INTO tab(name, field1) VALUES ('A', 1), ('A', 2), ('B', 4);

SELECT DISTINCT 
   name
   ,[sum]       = SUM(field1) OVER (PARTITION BY name)
   ,[substract] = SUM(-field1) OVER (PARTITION BY name) 
                  + 2*FIRST_VALUE(field1) OVER(PARTITION BY name ORDER BY ID)
FROM tab;

LiveDemo

Output:

╔══════╦═════╦═══════════╗
║ name ║ sum ║ substract ║
╠══════╬═════╬═══════════╣
║ A    ║   3 ║        -1 ║
║ B    ║   4 ║         4 ║
╚══════╩═════╩═══════════╝

Warning:

FIRST_VALUE is available from SQL Server 2012+

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
2

So you want to subtract the sum of the second to last from the first value?

You need a column to indicate the order. If you don't have a logical column like a datetime column you could use the primary-key.

Here's an example which uses common table expression(CTE's) and the ROW_NUMBER-function:

WITH CTE AS
(
    SELECT Id, Name, Field1,
           RN = ROW_NUMBER() OVER (Partition By name ORDER BY Id)
    FROM dbo.Table1
), MinValues AS
(
    SELECT Id, Name, Field1
    FROM CTE
    WHERE RN = 1
)
, OtherValues AS
(
    SELECT Id, Name, Field1
    FROM CTE
    WHERE RN > 1
)
SELECT mv.Name, 
       MIN(mv.Field1) - COALESCE(SUM(ov.Field1), 0) AS Subtract
FROM MinValues mv LEFT OUTER JOIN  OtherValues ov 
    ON mv.Name = ov.Name
GROUP BY mv.Name

Demo

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
0

Another method

declare @t table(name char(1), field1  int)
insert into @t
select 'A', 1 union all  
select 'A', 2  union all
select 'B', 4



select name, sum(field1) as addition,
             sum(case when sno=1 then field1 else -field1 end) as subtraction from 
(
    select *, row_number() over (partition by name order by (select 1)) as sno from @t 
) as t2 group by name
Madhivanan
  • 13,470
  • 1
  • 24
  • 29
0

On top of all the answers with algorithm to solve your problem, you have to keep in mind, that SUM is an aggregate on the set of data. There is no order of execution you can predict here.

That's why people are mentioning SUM (-field) - which is not really a subtract operation, it will just reverse the values and use SUM on the set of data as it really doesn't make any sense to 'subtract' on SET. Subtract would need you to define order of arguments selection to apply a binary operator on them.

Jakub Szumiato
  • 1,318
  • 1
  • 14
  • 19