Finding the Average ... BUT ... 1

[Albion]

I have a table with fields a, b, c, department, and year. Fields a,b, and c will be a number between 1 and 6, department will be x, y, or z, and year will be 2003, or 2004.

I need to find the average of all the records for fields a, b, and c where that record does not = 6 then group it by year and by department.

I.E. Averages would be....

a b c dept year
----------------------
1 6 3 x 2004
2 1 4 x 2004
6 3 2 y 2003
1 4 3 z 2005

The total average of a would be (1+2+1)/3, b would be (1+3+4)/3 and c would be (3+4+2+3)/4. Notice it only averages where the record does not = 6.

This is what I would like the output to look like.

(this example does not use the numbers from the example above)
a b c dept year
-----------------------
2.7 3.5 4.1 x 2003
1.8 2.1 3.9 x 2004
1.4 3.2 4.1 y 2003
4 3.1 3 y 2004
3.1 2.3 4.2 z 2003
1.4 3.2 3.2 z 2004

Can I do this in one query?

-Al

 

[carp]

Do you require a purely ANSI-compliant solution? And what RDBMS are you using?

 

[Albion]

The Database is MySQL and the FrontEnd is Access. I find that getting MySQL answers are much less frequent and I am good enough in both to allow me to translate.

 

[carp]

Well, if they support the CASE construct, then this might work:

SELECT AVG(CASE WHEN a = 6 THEN null
                ELSE a
           END) AS avga,
       AVG(CASE WHEN b = 6 THEN null
                ELSE b
           END) AS avgb,
       AVG(CASE WHEN c = 6 THEN null
                ELSE c
           END) AS avgc,
                dept, 
                year
FROM my_table
GROUP BY dept, year;

This worked on Oracle and I believe it's ANSI-compliant.
Corrections solicited.

 

[swampBoogie]

I believe it's ANSI-compliant.

It's not. Year is a reserved word and should be delimited.

 

[carp]

I stand corrected. With the hoopla Oracle made about having a CASE statement so they were even more ANSI-compliant, I made the (apparently bad) assumption that CASE must be part of the ANSI definition. So it goes.

 

[carp]

Well, maybe not.
At

http://www.sql.org,

"CASE" is listed as a reserved word in the SQL2 (1992) standard. If it's not part of the standard, then why are they reserving the word?

At any rate, Albion, you might try it to see if it fills your requirements.

 

[dnoeth]

CASE *is* Standard SQL, only a column named year is not allowed, because it's a reserved keyword. You'll just have to put it in double quotes to make it pure Standard SQL ;-)

And in that special case the CASE may be replaced by
AVG(NULLIF(a, 6))
and that's still Standard SQL...

Dieter

 

[Albion]

thanks Carp,

Not only did you solve my problem, but you showed me a new command which I didn't know about. Star for the response.

Thanks

-Al