[SOLVED] Need query to pull rows for current year but...

[5kyy8lu3]

 

Hi.    I have PHP 5.0.45.

 

I know I can somehow do this with PHP but I'm slowing realizing how powerful MySQL can be on its own and would like to see if there's a way I can do this using just a complex query.  I would like to pull ALL rows from my table from the current year BUT I want any rows from the same day (according to the timestamp) to be averaged into one value so i end up with 365 entries for the year (assuming they have at least one entry per day).

 

Is this possible?  I tried to play around with using GROUP BY and AVG but I can't seem to get it right, any advice/suggestions would be great.  If all else fails I'll just pull everything and do the work with php.  Thanks.

 

 

[5kyy8lu3]

 

would this work?  i might have to make a dummy table and add a full 365+ results with multiple rows for some days to try this out

 

"SELECT AVG(Speed) FROM MyTable GROUP BY DATE(DateAdded) ORDER BY DateAdded ASC"

 

 

[PFMaBiSmAd]

To get results for the current year, you would need -

 

WHERE YEAR(DateAdded) = YEAR(CURDATE())

 

and if that does not work because YEAR() expects a date, try this -

 

WHERE YEAR(DATE(DateAdded)) = YEAR(CURDATE())

[5kyy8lu3]

To get results for the current year, you would need -

 

WHERE YEAR(DateAdded) = YEAR(CURDATE())

 

and if that does not work because YEAR() expects a date, try this -

 

WHERE YEAR(DATE(DateAdded)) = YEAR(CURDATE())

 

well I know how to pull rows from this year, what I'm having trouble doing is bringing back 1 result per day, so I need 365 results back.  And if there are multiple rows for one day, I need it to average those into one result for that date.  I'm not sure if this makes sense lol.

 

"SELECT AVG(Speed) FROM MyTable GROUP BY DATE(DateAdded) WHERE YEAR(DateAdded) = YEAR(CURDATE()) ORDER BY DateAdded ASC"

 

Ok pretend table:

 

DateAdded  -    Speed

 

2009-02-01        0

2009-02-01        10

2009-02-02        6

2009-02-03        2

2009-02-03        4

 

And I want this as my result:

 

2009-02-01        5

2009-02-02        6

2009-02-03        3

[PFMaBiSmAd]

Once you put the WHERE clause before the GROUP BY clause, it works for me -

 

DateAdded  AVG(Speed)

2009-02-01  5.0000

2009-02-02  6.0000

2009-02-03  3.0000

 

If you tried something and it did not work, you would need to post your query/code for anyone to be able to help with what it was doing wrong.

[5kyy8lu3]

Once you put the WHERE clause before the GROUP BY clause, it works for me -

 

DateAdded  AVG(Speed)

2009-02-01  5.0000

2009-02-02  6.0000

2009-02-03  3.0000

 

If you tried something and it did not work, you would need to post your query/code for anyone to be able to help with what it was doing wrong.

 

yea i tried it and it didn't work until i put the where clause first and it works now, thanks for the help =)