Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Access SQl Query

Status
Not open for further replies.

chip12

Programmer
Jun 28, 2005
15
US
I need to group my data by month. The query I run gives the average of a select column of numbers but groups by days instead of months Below is my results. I need to group the averages by month instead of days. My code is simply not working Thanks in advance

6.762790698 1/2/2004
6.708717949 1/5/2004
6.72 1/6/2004
6.690588235 1/7/2004
 
My code is simply not working
Which code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
The code is Select avg(turn) from (tablename) group by funding_date. The averages are base on the individual day. I need the averages to be based on a month by month basis.
Thanks again
 
Why not simply this ?
SELECT Avg(turn) As Average, Format(funding_date, 'yyyy-mm') As [Month]
FROM tablename
GROUP BY Format(funding_date, 'yyyy-mm')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
I had a similar problem. An alternative you could try using would be -

Group by month(funding_date)

Since its solved its probably not important though.

 
Dibblet, and what happens if funding_date spans several years ?
 
I'll tell you in several years I only implemented it a couple of weeks ago!
 
what will happen is that all the January information will be averaged no matter the year. So the Jan 2001, 2002, 2003, 2004 and 2005 information will be in a single record - January. If you want to break down the average of January BY YEAR, you would need to use PHV's solution.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top