?
Solved

Average in a Query

Posted on 2007-03-21
6
Medium Priority
?
265 Views
Last Modified: 2010-03-20
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
Comment
Question by:thotovec
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18765744
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
 

Author Comment

by:thotovec
ID: 18766187
Need to specify . . . ?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18766234
what kind of database are you using?
I assume MS Access, but I want to avoid anyone guess around.
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

Author Comment

by:thotovec
ID: 18767091
I'm sorry - MS Access 2003.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 18767132
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
 

Author Comment

by:thotovec
ID: 18767437
Perfect!  Thank you!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Course of the Month12 days, 3 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question