How to minus instead of add in a Sum() like sql function

Question

I have a group by clause in a sql statement and need to use an aggregate function to minus all the values in each group instead of adding like the Sum() function.

i.e.

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

..so will evaluate 2+1 and return 3.

I need some way of doing 2-1 to return 1.

Hope this makes sense. Only way I can think of doing this would be to use CLR integration to make my own aggregate function.

Any other ideas?

Answer 1

How will you identify the item to be subtracted from?

Once that's been identified it's a SUM() multiplied by -1 and then added to that value.

Edit:

If it's the first value to be taken as the subtracted from then take that value, double it, then take away the sum of all the values. (Doubling it cancels out the effect of including it in the sum of all the values.)

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

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

Answer 2

SUM() works, as 2+1 == 1+2, whereas 2-1 != 1-2, so such a function would produce different results when the ORDER BY changes, if it were to exist.

Answer 3

I'm not sure WHY you'd want this, but I'd just SUM the negative values of the data returned:

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

Answer 4

From your question it isn't exactly clear what you want to do. If you want the sum of all the values as if they were negative then you can just perform a SUM(), and multiply by -1 to return a negative result.

In your example it looks like you want to get the sum of the first row in the table, minus all the other values. So if you had the values 10, 15, 5, 20 you'd want: 10 - 15 - 5 - 20. This value is the same as 10 - (15 + 5 + 20). You can get the first row in a table using LIMIT. We'll also need the primary key for the next stage:

SELECT primary_key AS pk, field FROM table LIMIT 1;

We can get the sum of all the other rows by excluding the above one:

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

You can then subtract the second value from the first.

Answer 5

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 to get reliable ordering you're probably going to need to use another select:

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