Help with summing data.

[terpster73]

Trying to get sum of parent and child accounts from the following:

ID Account Amount LeadingSpaces
13 4050.01 500 9
14 4050.06 3240 9
38 5010.01 4679.49 9
39 5010.1A 1263.97 12
41 5010.04 834.33 9
42 5010.4A 458.6 12
48 5050.01 1355.96 9
99 5430.01 1340 9
100 5430.21 1216.03 12

The Results I'm looking for

ID Account Amount LeadingSpaces
13 4050.01 500 9
14 4050.06 3240 9
38 5010.01 5943.46 9
41 5010.04 1292.93 9
48 5050.01 1355.96 9
99 5430.01 2556.09 9


The Id number is the PK and the exact order that the data is entered. The child accounts are determined by how many leadingspaces they have and will have the same first four digits of their parent account. The parent account will the the account that is directly above that has 9 leading spaces. Some accounts can have multiple sub accounts.

Anyone have a clue how to get what I'm looking for?

 

[markros]

SQL Server 2005 and up:

select ID, Account, Amount, LeadingSpaces from (select ID, Account, sum(Amount) over (partition by LEFT(account,4)) as Amount, row_number() over (partition by LEFT(account,4) order by ID) as RowNum from myTable) X where RowNum = 1

PluralSight Learning Library