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

# Sum repeated project budgets by region

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

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.

• ###### 2. Re: Sum repeated project budgets by region

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, 2012 1 Midwest \$100,000 \$10,000 Jan, 2012 2 Midwest \$200,000 \$20,000 Jan, 2012 3 Southwest \$50,000 \$5,000 Feb, 2012 1 Midwest \$100,000 \$15,000 Feb, 2012 2 Midwest \$200,000 \$25,000 Feb, 2012 3 Southwest \$50,000 \$75,000 Feb, 2012 4 Southwest \$25,000 \$5,000 Mar, 2012 1 Midwest \$100,000 \$5,000 Mar, 2012 3 Southwest \$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

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.