[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

Average in a Query

Hello Experts!

I am trying to calculate an average in a query, but I must be missing something in the syntax.

The table I'm working from has 7 columns - 1 "Name" column and 6 "Value" columns (V1, V2, etc).  Some of the value columns may be zero.  I have pulled all 7 columns into the query data fields.

My approach was to write an expression named "Average" in the 8th column in the query:
Average:  Avg([V1]+[V2]+[V3]+[V4]+[V5]+[V6])

Unfortunately, this simply sums the values.

Any ideas?
0
thotovec
Asked:
thotovec
  • 3
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
average function computes the average of all the grouped rows (if any).

in your case, you need to take the sum and divide by 6:
Average:  ([V1]+[V2]+[V3]+[V4]+[V5]+[V6])/6

if some values are zero, and you want to ignore them, you will have to "count" those.
however, there is no way of doing so that is works for all the databases with the same syntax, so you need to specify
0
 
thotovecAuthor Commented:
Need to specify . . . ?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what kind of database are you using?
I assume MS Access, but I want to avoid anyone guess around.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
thotovecAuthor Commented:
I'm sorry - MS Access 2003.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Average:  ([V1]+[V2]+[V3]+[V4]+[V5]+[V6])/ (
IIF( ISNULL(V1), 0, 1)  + IIF( ISNULL(V2), 0, 1) + IIF( ISNULL(V3), 0, 1 )  +    
IIF( ISNULL(V4), 0, 1)  + IIF( ISNULL(V5), 0, 1) + IIF( ISNULL(V6), 0, 1 )
)
0
 
thotovecAuthor Commented:
Perfect!  Thank you!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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