Calculating the average of four columns

[Cougar]

I am trying to calculate the average of three column, some of these columns
have zero and I don't want them included. See example of my table below
class_name Class_id GroupA GroupB GroupC
Brown C10004 4.1 2.0 3.3
Yellow C13380 0 0 1.8
Green C23414 3.6 0 6.3
Red C10066 2.2 3.8 0

 

[KARL DEWEY]

Try this --
SELECT Cougar.class_name, Cougar.Class_id,
([GroupA]+[GroupB]+[GroupC])/(IIf([GroupA]=0,0,1)+IIf([GroupB]=0,0,1)+IIf([GroupC]=0,0,1)) AS Group_Average
FROM Cougar;

 

[BruceM]

In addition to Karl's response, you may need to guard against all three
fields being 0. Maybe something like this as the expression:
IIf(([GroupA]+[GroupB]+[GroupC]) =
0,"",([GroupA]+[GroupB]+[GroupC])/(IIf([GroupA]=0,0,1)+IIf([GroupB]=0,0,1)+IIf([GroupC]=0,0,1)))
If a field's value is null rather than zero you will need to take extra
steps to deal with that (although it may be best to set the default value to
0, and not to allow nulls). I won't get into the details unless there is a
need.

 

[Ken Sheridan]

The problem really stems from a design flaw. By having three separate
columns you are doing what's known as 'encoding data as column headings'.
It’s a fundamental principle of the relational database model that data is
stored only as values at column positions in rows in tables.

What you should have is a separate table related to your current table on
its key (class_id presumably) with columns class_id, group and amount (or
whatever's an appropriate column name). The primary key of this table is a
composite one of class_id and group. You can then average the amount column
per group, excluding zeros with:

SELECT Classes.class_id, class_name,
AVG(amount) AS AverageAmount
FROM Classes INNER JOIN ClassGroups
ON Classes.class_id = ClassGroups.class_id
WHERE amount > 0
GROUP BY Classes.class_id, class_name;

Even though the Group is in the ClassGroups table you should also have a
Groups table with column Group as its primary key, and enforce referential
integrity between this and ClassGroups to ensure data integrity. Similarly
reverential integrity should be enforced in the relationship between Classes
and ClassGroups. The ClassGroups table is in fact modelling a many-to-many
relationship type between Classes and Groups.

Ken Sheridan
Stafford, England