troubleshooting Question

average of sums

Avatar of mirthless
mirthlessFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
8 Comments1 Solution431 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
deighton
prog

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros