# Calculating Running Total in SQL

I have been looking online for an answer but do not really know how to formulate correctly what I would like to achieve and whether it's possible, sorry if the question sounds dumb.

I have Site data with number of PC and AADTgroup 1,2 and 3 for each site. Data will remain sorted by ROWID and partitioned by AADTGroup.

My existing data is

I want the output with two more columns containing PCsum and NAF_Driver/Vehsum

where, I want the running total of PC in PCsum when it reaches to 5 and corresponding running total of NAF_Driver/Veh in NAF_Driver/Vehsum. Running total would start after reaching the limit 5 and with different AADTGroup.

The desired output is like below

I used SQL code

```DECLARE @point INT=5 ; ;WITH x(ROWID, AADT_Group, PC, R, PCsum) AS ( SELECT ROWID, AADT_Group, PC, ROW_NUMBER() OVER (ORDER BY ROWID), SUM(PC) OVER (PARTITION BY AADT_Group ORDER BY [ROWID], ROWID RANGE UNBOUNDED PRECEDING) FROM dbo.CAUrban4LaneDiv\$ ) SELECT x.ROWID, x.AADT_Group, x.PC, x.PCsum FROM x WHERE x.PCsum < @point ORDER BY x.ROWID;```

But it did't give me the desired result TIA

• I also want the running total be partitioned by AADT group Commented Jun 14, 2019 at 15:02
• You can edit the question and add that comment to it. Commented Jun 14, 2019 at 15:05
• I am new and couldn't find the option, thanks a lot. Commented Jun 14, 2019 at 15:09
• The desired output doesn't make much sense. I don't see a running total. For example in the very first row, why PCSum should be 6 when PC is 0? Commented Jun 14, 2019 at 18:53
• Hi ypercube, thanks for your concern, actually the rows for which the cumulative sum would reach 5, I need the proportion in each row using this cumulative sum in each row Commented Jun 15, 2019 at 14:15

``````DECLARE @point INT = 5;

WITH cte AS (
SELECT
ROWID,
PC,
NAF_Driver_Veh,
ROW_NUMBER() OVER (PARTITION BY AADT_Group ORDER BY ROWID) AS R,
SUM(PC) OVER (PARTITION BY AADT_Group ORDER BY ROWID) AS PCsum,
SUM(NAF_Driver_Veh) OVER (PARTITION BY AADT_Group ORDER BY ROWID) AS NAF_Driver_VehSum
FROM dbo.CAUrban4LaneDiv\$
)
SELECT
cte.ROWID,