As minus instead of adding Sum () function as sql

Question

I have a group by clause in a sql expression and you need to use the aggregate function to minus all the values ​​in each group, and not as a Sum () function.

i.e.

SELECT Sum(A) 
FROM (  
  SELECT 2 AS A
  UNION 
  SELECT 1) AS t1

.. therefore it will evaluate 2 + 1 and return 3.

I need to make method 2-1 to return 1.

Hope this makes sense. The only way I can do this is to use the CLR integration to create my own aggregate function.

Any other ideas?

Answer 1

How will you identify the item to be subtracted?

After it has been determined, a SUM(), multiplied by -1and then added to this value.

Edit:

, , , . ( .)

select top 1 @var = [value]
from myTable 
order by [some condition] 

select @minused = (2 * @var)  - sum([value]) 
from myTable 

Answer 2

, . , , , SUM() -1, .

, , . , 10, 15, 5, 20, : 10 - 15 - 5 - 20. , 10 - (15 + 5 + 20). , LIMIT. :

SELECT primary_key AS pk, field FROM table LIMIT 1;

, :

SELECT SUM(field) FROM table WHERE primary_key != pk;

.

Answer 3

Reading your comments, I think you want something like this:

SELECT SUM(CASE WHEN ROWNUM=1 THEN 2*A ELSE -A END) 
FROM foo

Although for a reliable order, you probably need to use another option:

SELECT SUM(b) 
FROM (
  SELECT CASE WHEN ROWNUM=1 THEN 2*a ELSE -a END AS b
  FROM foo
  ORDER BY ???
);

Answer 4

SUM () works like 2 + 1 == 1 + 2, while 2-1! = 1-2, so this function will give different results when ORDER BY changes if it should exist.

Answer 5

I am not sure WHY you want this, but I would only SUM negative return values:

SELECT Sum(A) 
FROM (  
  SELECT (2 * -1) AS A
  UNION 
  SELECT (1)) AS t1