Hierarchy based aggregation

Posted by Ganapathy Subramaniam on Stack Overflow See other posts from Stack Overflow or by Ganapathy Subramaniam
Published on 2010-03-12T06:45:51Z Indexed on 2010/03/12 6:47 UTC
Read the original article Hit count: 556

Filed under:

hierarchy

|

aggregation

|

SQLServer

I have a hierarchy table in SQL Server 2005 which contains employees -> managers -> department -> location -> state.

Sample table for hierarchy table:

ID  Name        ParentID    Type
1   PA      NULL        0 (group)
2   Pittsburgh  1       1 (subgroup)
3   Accounts    2       1 
4   Alex        3       2 (employee)
5   Robin       3       2
6   HR          2       1
7   Robert      6       2

Second one is fact table which contains employee salary details ID and Salary.

Sample data for fact table:

ID    Salary
4     6000
5     5000
7     4000

Is there any good to way to display the hierarchy from hierarchy table with aggregated sum of salary based on employees. Expected result is like

Name          Salary
PA                15000   (Pittsburgh + others(if any)) 
  Pittusburgh     15000   (Accounts + HR)
    Accounts      11000   (Alex + Robin)
      Alex         6000   (direct values)
      Robin        5000
    HR             4000
      Robert       4000


In my production environment, hierarchy table may contain 23000+ rows and fact table may contain 300,000+ rows. So, I thought of providing any level of groupid to the query to retrieve just its children and its corresponding aggregated value. Any better solution?

© Stack Overflow or respective owner

Related posts about hierarchy

Hierarchy based aggregation

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a hierarchy table in SQL Server 2005 which contains employees - managers - department - location - state. Sample table for hierarchy table: ID Name ParentID Type 1 PA NULL 0 (group) 2 Pittsburgh 1 1 (subgroup) 3 Accounts 2 1 4 Alex 3… >>> More
javascript complex recurrsion [on hold]

as seen on Programmers - Search for 'Programmers'
Given Below is my data in data array. What i am doing in code below is that from that given data i have to construct json in a special format which i also gave below. //code start here var hierarchy={}; hierarchy.name="Hierarchy"; hierarchy.children=[{"name":"","children":[{"name":"","children":[]}]}]; … >>> More
Visualize flowchart diagram with multiple end symbols

as seen on Programmers - Search for 'Programmers'
I am looking for a standardize way to visualize the following hierarchical logic: The state of the thread is represented by the answers to the hierarchical set of question You can read this listing like a flowchart, you iterate over the questions decide, go one step deeper and so on. Therefore I… >>> More
Visualize flowchart diagram with multiple end symbols

as seen on Programmers - Search for 'Programmers'
I am looking for a standardize way to visualize the following hierarchical logic: The state of the thread is represented by the answers to the hierarchical set of question You can read this listing like a flowchart, you iterate over the questions decide, go one step deeper and so on. Therefore I… >>> More
class hierarchy design for small java project

as seen on Programmers - Search for 'Programmers'
I have written a java code which does following:- Main goal is to fetch emails from (inbox, spam) folders and store them in database. It fetches emails from gmail,gmx,web.de,yahoo and Hotmail. Following attributes are stored in mysql database. Slno, messagedigest, messageid, foldername, dateandtime… >>> More

Related posts about aggregation

Hierarchy based aggregation

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a hierarchy table in SQL Server 2005 which contains employees - managers - department - location - state. Sample table for hierarchy table: ID Name ParentID Type 1 PA NULL 0 (group) 2 Pittsburgh 1 1 (subgroup) 3 Accounts 2 1 4 Alex 3… >>> More
Microsoft DBMS on-the-fly aggregation

as seen on Stack Overflow - Search for 'Stack Overflow'
Some time ago i was reading an article about new MS DBMS technology. It's some kind of OLAP but on the fly. This technology can bind to data flows and then provide a real time aggregation. So the question is "what is it's name?". I need such a technology now but can't remember it's name... Or maybe… >>> More
Dependency injection and aggregation/association

as seen on Stack Overflow - Search for 'Stack Overflow'
In both association and aggregation, one class maintains a reference to another class. Then, does constructor injection imply composition? Going by the same logic, is it safe to say that setter injection leads to an association, and not an aggregation? >>> More
Solaris 11 Update 1 - Link Aggregation

as seen on Oracle Blogs - Search for 'Oracle Blogs'
Solaris 11.1 No início desse mês em um evento mundial da Oracle chamado Oracle Open World foi lançada a nova release do Solaris 11. Ela chega cheia de novidades, são aproximadamente 300 novas funcionalidade em rede, segurança, administração e outros. Hoje vou falar de uma funcionalidade de… >>> More
Generic Aggregation of C++ Objects by Attribute When Attribute Name is Unknown at Runtime

as seen on Stack Overflow - Search for 'Stack Overflow'
I'm currently implementing a system with a number of class's representing objects such as client, business, product etc. Standard business logic. As one might expect each class has a number of standard attributes. I have a long list of essentially identical requirements such as: the ability to… >>> More