Running Sum in Query

[handlebarry]

I have a query with three columns, Estate Code, Block code, Total Properties:

Estate_Code Block_Code Total_Properties
10001 10 20
10001 20 17
10002 10 5
10002 20 6

What i want to do is add a fourth column that would show the total properties by estate code. In the example above it would show 37 and 11

can anyone please help?
thanks in advance

 

[PHV]

You may try something like this:
SELECT A.Estate_Code, A.Block_Code, A.Total_Properties, Sum(B.Total_Properties) AS Running_Sum
FROM yourTable As A INNER JOIN yourTable As B
ON A.Estate_Code = B.Estate_Code AND A.Block_Code >= B.Block_Code
GROUP BY A.Estate_Code, A.Block_Code, A.Total_Properties;

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