Averaging by year 1

[gemoon]

This seems like an easy problem, but for the life of me I can’t get the solution. I’m trying to write a query that averages values by year. I think I’m on the right track by using the DatePart function, but I keep getting a compile error message. I have two tables with 2 & 3 fields respectively

tblA
A date

tblB
B A X

A and B are the primary keys (both are integers). date has format dd/mm/yy and x is a number

The query that I’ve created is as follows
SELECT DatePart('yyyy',[tblA].[date]) AS Expr1, Avg(tblB.X) AS AvgOfX
FROM tblA LEFT JOIN tblB ON tblA.A = tblB.A
GROUP BY DatePart('yyyy',[tblA].[date]);

Thanks in advance for the help,
Ed

 

[r937]

your query actually looks okay

try this, it should work too --

SELECT year(tblA.[date]) AS theYear
, avg(tblB.X) AS AvgOfX
FROM tblA LEFT outer JOIN tblB
ON tblA.A = tblB.A
GROUP BY year(tblA.[date])


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[gemoon]

Thank you very much. I tried your solution and was still getting the same compile error. This time it refered to 'year(tblA.[date])'. I think the problem is with my database. It has become corrupted or something similar I coppied these two tables and the query into a new database and the query worked fine. My query above also worked in the new database. Luckily I don't have too many tables to transfer over.

Thanks for the help,
Ed.