3 Replies Latest reply on Aug 28, 2012 5:32 AM by Elad Lanir

    Sum repeated project budgets by region

    Lance Leonard

      I have project data grouped by Period & Region. The budget amount is repeated and is unique by the project. I need to sum this at the regional level. For example, the data looks like this:

       

      Period
      Project IDRegionBudgetRevenue
      Jan, 20121Midwest$100,000$10,000
      Jan, 20122Midwest$200,000$20,000
      Jan, 20123Southwest

      $50,000

      $5,000
      Feb, 20121Midwest$100,000$15,000
      Feb, 20122Midwest$200,000$25,000
      Feb, 20123Southwest$50,000$75,000

       

      I want my output to look like this:

       

       

      Region

      BudgetRevenue
      Midwest$300,000$70,000
      Southwest$50,000$80,000

       

      So, essentially, I'm having a really hard time getting the Budget numbers to sum properly along the Region. Any advice is greatly appreciated.

        • 1. Re: Sum repeated project budgets by region
          Vladislav Grigorov

          Hello Lance,

           

          How about using count distinct to determine the number of different periods you work with and then dividing the sum of all budgets by the result to get to the true budget amount. I attach an example, and hoe it's helphul a little bit.

           

          Vlad

          • 2. Re: Sum repeated project budgets by region
            Lance Leonard

            Thanks, Vlad. That would make sense if the data were consistent by period like in my first example, but in reality it's more complex. Let's add a fourth project that had activity in February, and assume Project 2 didn't have activity in March, so now our data looks like this ...

             

            Jan, 20121Midwest$100,000$10,000
            Jan, 20122Midwest$200,000$20,000
            Jan, 20123Southwest

            $50,000

            $5,000
            Feb, 20121Midwest$100,000$15,000
            Feb, 20122Midwest$200,000$25,000
            Feb, 20123Southwest$50,000$75,000
            Feb, 20124Southwest$25,000$5,000
            Mar, 20121Midwest$100,000$5,000
            Mar, 20123Southwest$50,000$1,000

             

            ... Which means our output now should look like this ...

             

             

            Midwest$300,000$75,000
            Southwest$75,000$86,000
            • 3. Re: Sum repeated project budgets by region
              Elad Lanir

              hi lance,

               

              i think i would read the same data twice:

              once with distinct, only for project id, area and budget

              second will be a regular select query, something like

                   select region, sum(revenue) from <table name> group by region

               

              than join between the to results by region.

              i think that should answer your need.