Running Sum between two tables

[dcwave]

I have this query ti give me a running sum from my sales table.

SELECT  A.RSMID, A.Amount, (SELECT Sum(B.Amount) FROM Sales B WHERE B.RSMID=A.RSMID ) As RunningAmount 
FROM Sales A

This gives me:
RSMID, Amount, RunningAmount
37 55000 96000
37 41000 96000
38 20000 20000




I need to pull a user name from my RSM table and match on RSMID in the sales table.

I have tried doing stuff in the WHERE statement but come up with the info displayed incorrectly.

Any help would be great. Thanks.

 

[PHV]

Something like this ?
SELECT A.RSMID, RSM.Name, A.Amount, (SELECT Sum(B.Amount) FROM Sales B WHERE B.RSMID=A.RSMID ) As RunningAmount
FROM Sales A INNER JOIN RSM ON A.RSMID=RSM.RSMID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dcwave]

That worked - thanks. I knew it had to be some thing simple that I was missing - joins always throw me for a loop - I never know when I should use them.