0

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

Existing Data

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

output

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 – Sadia Sharmin Jun 14 at 15:02
  • 1
    You can edit the question and add that comment to it. – McNets Jun 14 at 15:05
  • I am new and couldn't find the option, thanks a lot. – Sadia Sharmin Jun 14 at 15:09
  • 1
    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? – ypercubeᵀᴹ Jun 14 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 – Sadia Sharmin Jun 15 at 14:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.