# (500 pts.) Opposite of sum in a select query

Hi All,
This might be a simple one, but I can't seem to figure it out.  500 pts. because I need it right away.

I have a query that has 2 records per ID in it.  One for the run month and one for the prior month.

for example:

AsOf      ID      Shift
30-Jun-06      9113      -2.23096039
31-Jul-06      9113      0.278805355
30-Jun-06      9103      2.8866533
31-Jul-06      9103      1.297256548

What I want is the opposite of sum for each ID.
for example:

ID      NewShift
9113      2.509765745
9103      -1.589396753

Any help would be greatly appreciated.
Thanks,
Dave
Patrick Matthews

Hi Cristal638,

SELECT ID, -Sum(Shift) AS NewShift
FROM YourTable
GROUP BY ID

Regards,

Patrick
just multiply the  field with min date with -1

Can any body tell me what the relation is between the data and the result?
peter57r

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ray,

> Can any body tell me what the relation is between the data and the result?

Good question.  I assumed that the example was incorrect, and went with a very literal interpretation of the question :)

Regards,

Patrick
You know me Patrick - I never assume anything:-/
Cristal638

Actually the example is the result I'm looking for.

July Shift minus Jun Shift equals NewShift

31-Jul-06      9113      0.278805355
30-Jun-06      9113      -2.23096039

2.509765745 = NewShift

Hi Patrick,
Unfortunately -sum gives me the wrong result.  Arg...

I'm checking Pete's to see if it gives me what i'm looking for.
As always, thank you all for your help...
31-Jul-06     9113     0.278805355
30-Jun-06     9113     -2.23096039

but where do those humbers come from?
humbers=numbers!

We have a winner...  Thank you all, but Pete gave me exactly what I was looking for...

Thanks again,
Dave
Cristal638,

SELECT DISTINCT t1.ID,
((SELECT -t2.Shift
FROM SomeTable AS t2
WHERE t2.ID = t1.ID And t2.AsOf = #30 Jun 2006#) +
(SELECT -t3.Shift
FROM SomeTable AS t3
WHERE t3.ID = t1.ID And t3.AsOf = #31 Jul 2006#)) AS NewShift
FROM SomeTable AS t1
ORDER BY t1.ID

Regards,

Patrick