Solved

query - averages with different criteria

Posted on 2014-02-24
3
359 Views
Last Modified: 2014-02-24
Dear experts -
I have a workhorse table of expense items. Each item has a person, an action code, rate paid, language, etc.

Now, I need to generate a relatively long list of AVERAGES, by LANGUAGE - for example, for action 'x' where another field = y, for action 'z' where another field has a different value, etc.

Is there some way to do those other than creating a separate query for each average needed?

The point is - it would be easy if the criteria for the underlying dataset were identical (e.g., do an average of field x, then field y, and then field z where all the records have the following criteria...). But that's not the case.

Can I use some sort of IIF or other construct that would allow me to do this?

Thanks!
0
Comment
Question by:terpsichore
3 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39882393
Hi terp,

Can you give us a bit more detail about what you're trying to do?  Perhaps an example?

And are you looking for a solution that runs in MS Access or on another engine.  The SQL can be quite different.


Kent
0
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 500 total points
ID: 39882476
You could use SUM / COUNT, where you use a case-statement to specify criteria (note: I used NULLIF to replace zero with null, so you don't divide by zero; you may add more and/or in the case-statement), example:
select	avg1 = SUM(case when cond1 = 1 then value end) / NULLIF(COUNT( case when cond1 = 1 value end),0)
,	avg2 = SUM(case when cond2 = 2 then value end) / NULLIF(COUNT( case when cond2 = 2 value end),0)
from	workhorse 

Open in new window

0
 

Author Closing Comment

by:terpsichore
ID: 39882630
I think this should be perfect. thank you.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

860 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