• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 992
  • Last Modified:

Calc Average Value for Values in Row of Matrix

I've have a returns matrix set up and I'd like to calculate the average values across a row.  Are there any built-in functions to do something like this?  I'll need to account for nulls etc.  Max there would be 12 columns of data.

Ex:
2,5,4, null are all the values in a row so the avg would be 3.66

Thanks!
0
gigglick
Asked:
gigglick
  • 4
  • 2
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
No, nothing built in, you will have to code it yourself:

SELECT ...,
    (ISNULL(col1, 0) + ISNULL(col2, 0) + ... + ISNULL(col12, 0) /
    NULLIF(
    (CASE WHEN col1 IS NULL THEN 0 ELSE 1 END +
     CASE WHEN col2 IS NULL THEN 0 ELSE 1 END +
     ... +
     CASE WHEN col12 IS NULL THEN 0 ELSE 1 END), 0) AS AvgAcrossCols,
    ...
0
 
messen1975Commented:
" Max there would be 12 columns of data."

Is there a variable number of columns displayed?
0
 
gigglickAuthor Commented:
Hi - didn't think there was anything built - DARN!  The storage table for the matrix will always have twelve columns, however, not all accounts on our end will have enough data to fill all twelve columns of the matrix.  Also, I am giving the user the option to choose the number of periods/columns they want.  So the problem is substituting 0 in for null will result in the wrong average.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Scott PletcherSenior DBACommented:
Not with the code I posted it won't :-).  When I divide to get the average, I *don't count* those that are NULL, only those that aren't NULL.
0
 
messen1975Commented:
Scotts answer should work if there are a fixed number of columns.
0
 
gigglickAuthor Commented:
Hi Scott -
I should have mentioned that I wasn't directing that at your reponse...but code I was toying with on my end.  I'm trying yours out now...

Giggs
0
 
gigglickAuthor Commented:
Hi Scott -

Worked perfectly and quick!! Thanks so much for your help.

Giggs
0
 
gigglickAuthor Commented:
How does he always get it right?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now