# average of sums

I have a need to create a sum of a set of data that is offset for each column. Then I need to crate an average of each sum.  Here is the data set:

287849      0      0      0      0      0      0      2011-04-01
305175      0      0      0      0      0      0      2011-05-01
321305      0      0      0      0      0      0      2011-06-01
349804      0      0      0      0      0      0      2011-07-01
385270      0      0      0      0      0      0      2011-08-01
396388      0      0      0      0      0      0      2011-09-01
398585      0      0      0      0      0      0      2011-10-01
391251      4856      0      0      0      0      0      2011-11-01
410350      38764      20248      0      0      0      0      2011-12-01
399031      73552      56651      8353      0      0      0      2012-01-01
437927      123331      116539      71708      9266      0      0      2012-02-01
479173      220768      215429      178598      119074      34615      0      2012-03-01
513639      282400      277061      242204      203727      127661      40759      2012-04-01
480104      276591      271252      239102      203081      154021      86797      2012-05-01
344447      182753      179500      154129      122132      81242      55949      2012-06-01
283951      143614      140753      116574      85270      58826      39450      2012-07-01
263782      137062      134201      111321      82022      55578      37887      2012-08-01
231184      131620      129656      106776      79116      53922      36431      2012-09-01
184214      114084      113470      93999      69307      51974      36331      2012-10-01

The left column is my "control" column. Of the remaining right hand columns I need to select and create a avergae of each alternate row summed up. I need this sum for the first three instances of sums. I am not even sure how to explain in words, I can do the work in excel but I need it in a query do I can plot on a report. Here is what I would need from this data set:

(4856+20248+8353+9266+34615+40759)/6 = 19682.83  as neg1
(38764+56651+71708+119074+127661+86797)/6 = 71522.14 as neg2
(73552+116539+178598+203727+154021+55949)/6 = 97798.25 as neg3

Each of these averages need to be unique objects that I can add them to a specific value in the control column. Once I get the averages, I can then add them to the appropriate months value from the control column.

In this specific case I will be adding 19682.83 + 4800104 and
71522.14 + 344447 and
97798.25 + 283951
aikimark

Do these columns have names?
``````287849      0      0      0      0      0      0      2011-04-01
305175      0      0      0      0      0      0      2011-05-01
321305      0      0      0      0      0      0      2011-06-01
349804      0      0      0      0      0      0      2011-07-01
385270      0      0      0      0      0      0      2011-08-01
396388      0      0      0      0      0      0      2011-09-01
398585      0      0      0      0      0      0      2011-10-01
391251      4856   0      0      0      0      0      2011-11-01
410350      38764  20248  0      0      0      0      2011-12-01
399031      73552  56651  8353   0      0      0      2012-01-01
437927      123331 116539 71708  9266   0      0      2012-02-01
479173      220768 215429 178598 119074 34615  0      2012-03-01
513639      282400 277061 242204 203727 127661 40759  2012-04-01
480104      276591 271252 239102 203081 154021 86797  2012-05-01
344447      182753 179500 154129 122132 81242  55949  2012-06-01
283951      143614 140753 116574 85270  58826  39450  2012-07-01
263782      137062 134201 111321 82022  55578  37887  2012-08-01
231184      131620 129656 106776 79116  53922  36431  2012-09-01
184214      114084 113470 93999  69307  51974  36331  2012-10-01 ``````

Is this data you have in your SQL Server database?
deighton

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sometimes it is easier to calculate in the application, and sometimes it is easier in the database.  I can't see where this is being used, so it is difficult to suggest anything.
context is king

Sorry...I am running this in a stored procedure...then will be attaching the data source to an SSRS report. I can name the columns anything I want as it is simply a temp table within the procedure. I have a good portion of code that has crunched financial data to place it into monthly totals. Each row is a monthly total with each column being for different period of time. I am plotting a forecasted change based on historical data.
ok.  I asked about the column names in order to better understand what you are trying to do (context).

Let me ask this a different way...
Please explain the context or reasoning behind summing data from different controls across time?

I've the nagging suspicion that you have boxed yourself into a corner with your prior data manipulation.

==========
Possible solution paths:
* traverse the data with a cursor
* copy the first 9 rows to a temp table and the iterate the temp table three times, peeling off the top non-zero values each iteration of the columns.  During the iteration, the top/first non-zero value in each is updated to a zero in preparation for the next iteration.
SSRS has functions that you can use or create to sum for a column:

Example:

``=Sum(Fields!TotalAmount.Value, "MyDataSet")``