Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2012
    Posts
    2

    Unanswered: Consolidation/select syntax problems

    Hi all

    Im in doubt if its possible to make a select statement which enables me to consolidate multiple lines in the same table.

    I have a table with a lot of companies and figures for each company.
    Some of the companies owns some of the other companies in the table and in reverse, some of the companies are owned by some of the companies in the table.

    I have a lot of columns, but basically the most important columns are:

    Company Name, Company Mother , Company Daughter.
    Each company has also a revenue column.

    What I want to do is to consolidate all figures for absolute mothers e.i. companies which are not owned by another company in the list.


    I therefore need a select statement which says something like:
    Get the revenue of companies not owned by another company (e.i. absolute mother). Add to this, the revenue of all its daughters. Add to this the revenue of all the daughters daughters etc. until there are no daughters left.

    In other words - aggregate the revenue for all the companies in the group under the name of the ultimate parent company.

    I can easily select and add the revenue for the first level of direct daughters, but I dont know how many more daughters the daughters has etc.

    Do anyone has suggestions as how to solve this?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's how --> recursive CTE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2012
    Posts
    2
    THANKS! Exactly what I needed

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •