Running Total SQL for vb.NET project

[chasadjee]

I am writing an application which calculates profit from 3 column values. The SQL is as follows in vb dataset designer;

SELECT ID, Sale - (Weight * 20) * (Rate -5) AS Profit
From AgencyRegister
WHERE (DatePart('yyyy', ArrivalDate) = ?)
ORDER BY ArrivalDate

I want to add another column of 'Total' that may display running profit an a separate column. I've tried;

SELECT        a.ID,  a.Sale - (a.Rate - 5) * (a.Weight * 20) AS Profit, SUM(b.Sale - (b.Rate - 5) * (b.Weight * 20)) AS Total
FROM            AgencyRegister a, AgencyRegister b
WHERE        (DatePart('yyyy', a.ArrivalDate) = ?) AND a.ID = b.ID
ORDER BY a.ArrivalDate

But vb SQL designer automatically converts it to

SELECT a.ID, a.Sale - (a.Rate - 5) * (a.Weight * 20) AS Profit, SUM(b.Sale - (b.Rate - 5) * (b.Weight * 20)) AS Total
FROM (AgencyRegister a INNER JOIN
     AgencyRegister b ON a.ID = b.ID)
WHERE (DatePart('yyyy', a.ArrivalDate) = ?)
ORDER BY a.ArrivalDate

But this code does not run and displays an error.
Please suggest me how do i fix it.

[adam_k]

The problem isn't the conversion from old syntax to join syntax, but that you are missing a group by.
Also I don't think that you can get a running total with a.ID = B.ID I think you need to a.ID >= b.ID (in order to sum all records up to the current one)

[chasadjee]

I changed it to

a.ID >= b.ID

and also added

GROUP BY a.ID

but it still does not run.

[adam_k]

Change your group by to

GROUP BY a.ID, a.Sale - (a.Rate - 5) * (a.Weight * 20)

(Column Profit is a calculation not an aggregate).
Also, is there an error?

[chasadjee]

Thank you it really works. What should I do if I have to add some more columns in this like 'AgencyCode, Rate, Sale' etc..., are they also be grouped using GROUP BY statement or some else statement can give simple columns in addition.
Thanks in anticipation.

[chasadjee]

Thank you for your cooperation, I have done the later part myself. My task is won't be completed without your suggestions. :) thank you :)