Link to home
Start Free TrialLog in
Avatar of Cristal638
Cristal638

asked on

(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
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

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?
ASKER CERTIFIED SOLUTION
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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:-/
Avatar of Cristal638
Cristal638

ASKER

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