Using Aggregate Functions

S

SQL newbie

Can someone show me an example of how to obtain the
average value of multiple (as opposed to single) columns
in a table using the AVG() function? Thank you.
 
T

Tom Ellison

Dear New:

This should not happen. If you have multiple columns in a table that
have numbers in them that have the same meaning, so as to be able to
produce an average together, then the table design is almost certainly
improper. If you'll provide details of the table, I'll make some
general recommendations.

You could use a SUM() for each of the columns and add them together.
Use 0 in the sum for any null values with the Nz() function. Divide
that by a COUNT() of those same columns. That would be an average.

However, I do not recommend this as the best approach. An improved
table design is much preferred.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
S

SQL newbie

Tom:

Thanks for your prompt reply!

As per your request, here is the example SQL statement I
issued against my example table;

SELECT AVG (prod_price) as avg_price
FROM products
WHERE vend_id = 'DLL01';

As we know, the WHERE clause in this statement will only
return the average price for the DLL01 column.

The book I am using to teach myself states the following:

"AVG() may only be used to determine the average price of
a specific numeric column. The column name must also be
specified as the function paramater. To obtain the
average value of multiple columns, multiple AVG()
functions must be used."

Unfortunately, my book has inconveniently left out the
description of how to use multiple AVG() functions in a
single SELECT statement (if that is possible).

The exmaple .mdb file that I am using can be found at the
book author's website
http://www.forta.com/books/0672325675/ and then selecting
the Microsoft Access Database link. It is a very small
zip file (and don't worry, it's not infected with
anything ;)

Thanks for your help!
 
J

John Spencer (MVP)

Not clear what you are trying to do.

If you are trying to get multiple averages for Prod_Price on different vendors.


SELECT Vend-id, AVG (prod_price) as avg_price
FROM products
WHERE vend_id IN ('DLL01','DLL02')
GROUP BY vend_id

That will give you the average price for vend_id DLL1 and DLL2

SELECT Vend-id, AVG (prod_price) as avg_price
FROM products
GROUP BY vend_id

Will give you the average prod_price for each vend_id.

If what you want is something differnet can you specify what you do want?
Perhaps entering a few sample records and the results you expect to get will
make it clear.
 
T

Tom Ellison

Dear New:

I would have to say your book is wrong. Sorry. Well, perhaps it is
your interpretation of the book that is wrong. But here's the point.

Let's say you have a table with 3 rows and 2 columns to average. The
values stored are:

7 3
8 null
6 2

The answer is (7 + 8 + 6 + 3 + 2) / 5 which it 5.2 Nulls do not
affect the average. If you average the two fields separately, you get
avarages of 7 and 2.5. If you then average those you get 4.75.
That's a different, and wrong answer. The point is that the presence
of nulls will decrease the weight of the column in which they occur.
However, you may determine your own definition of average if you wish.
So, whether this is really wrong or not may depend on you. But, it is
undoubtedly a different value for average than the method I suggested.

To product the result I called "correct" you would SUM the two columns
and add together the sums. You sould also COUNT the specific columns,
which will skip counting the nulls in those columns. Add together all
the SUMs and divide that by the value formed by adding together all
the counts. That give the value the way I recommended. Using the
sample data above:

(SUM(Nz(Col1, 0)) + SUM(Nz(Col2, 0))) / (COUNT(Col1) + COUNT(Col2))

This would be (21 + 5) / (3 + 2) which is 5.2. That's how this works.
I don't believe this can be done using the AVG() function at all.

However, I still recommend a properly normalized table structure that
will permit you to handle this in one column, the way I first
explained it.

Note: if you do not now, and are certain you never will have nulls in
the table, then (AVG(Col1) + AVG(Col2)) / 2 would work as well, as I
expect your book intends. But, as illustrated, when there are nulls
in these columns, it will not always give the "correct" answer.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Top