I have a SQL table which holds a parent-child relationship in it own schema via a ParentId column. There are some other tables (Sale, etc.) linked to it via the LocationId column. I am after grouping data based on the second level parents (Code: xxxxx.xxxxx) and then apply some aggregate function such as SUM and COUNT to its children and itself.
Expectation Result for seeing the total sales for 2 regions(Victoria, South Australia) would be something like:
DisplayName TotalSale Victoria $25,000 South Australia $30,000