Avatar of mirthless
mirthless
Flag for United States of America asked on

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
Microsoft SQL Server 2008Microsoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
mirthless

8/22/2022 - Mon
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 

Open in new window


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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Bob Learned

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.
aikimark

context is king
Your help has saved me hundreds of hours of internet surfing.
fblack61
mirthless

ASKER
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.
aikimark

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.
Bob Learned

SSRS has functions that you can use or create to sum for a column:

Example:

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

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
mirthless

ASKER
Just what I needed.

Thanks