Average Of A Column And For A Particular Month

[wright67uk]

I have a MySql database

 

with a similar layout to the below example but with a lot more users, clubs,

dates etc.

 

Score_table

 

Dates |club |user_id |score

 

1/10/12 |3 Iron | 123 | 112

12/10/12 |3 Iron | 123 | 156

 

 

What im trying to do is run a query that will display the average score for each month for a particular user;

 

Select 'Average Score' from 'score_table' where 'club' = 3 Iron and 'user_id' = 123 AND date = october

 

Does MySql have this capability and how would i correctly write the above query?

 

I would eventually need to go a step further and write;

 

 

Select 'Average Score' from 'score_table' where 'club' = 3 Iron and 'user_id' = 123 FOR each month in 2012

 

if this isnt possible, then how close can you get?

[Barand]

MySQL has many date functions, but they work with correctly formatted dates. Use DATE or DATETIME format yyyy-mm-dd. Your format can't be used for comparing dates. You will need to use STR_TO_DATE() to reformat to DATE type then USE AVG(score) to calc the average score. You will also need to GROUP BY month

Edited December 7, 2012 by Barand

[Barand]

SELECT MONTH(STR_TO_DATE(dates, '%d/%m/%y')) as `month`, AVG(score) as avscore
FROM score_table
WHERE club = '3 iron' AND user_id = 123 AND YEAR(STR_TO_DATE(dates, '%d/%m/%y')) = '2012'
GROUP BY `month`

[wright67uk]

Wow! Thanks Barand, I didn't realise that SQL could do all of this. Thankyou very much!!!

 

After changing the query slightly to fit my table structure (I already had a separate year column etc) I used this...;

 

SELECT MONTH( STR_TO_DATE( sdate, '%d/%m/%y' ) ) AS `month` , AVG( yard ) AS avscore

FROM `snag`

WHERE club = '3 iron'

AND user_id =5

AND YEAR = '2012'

GROUP BY `month`

 

which gave me the following results;

 

month | avscore

8 112.0000 11 110.000

 

which is brilliant. is it then possible to return an average score of '0' for months with no score, so my table would look more like;

 

month | avscore

1 | 0

2 | 0

3 | 0

4 | 0

5 | 0

6 | 0

7 | 0

8 | 112.000

9 | 0

10 | 0

11 | 110.000

12 | 0

 

This way i could feed this info straight into a graph!

[Barand]

To do that you need all month numbers to be present.

 

Create a table (month_table) with 12 records, 1-12 in the "month" field and use that in the query

 

SELECT month_table.month , AVG( yard ) AS avscore
FROM  month_table
   LEFT JOIN `snag` ON month_table.month = MONTH( STR_TO_DATE( sdate, '%d/%m/%y' ) )
WHERE club = '3 iron'
AND user_id =5
AND YEAR = '2012'
GROUP BY `month`

[wright67uk]

Thanks for the reply. I did exactly that, however I still get the same records returned.

 

8 112,0000

11 110.0000

[wright67uk]

IVe been reading up on

 

IsNull and Coalesce

 

I didn't really understand the document on this, but I'm guessing somewhere in my query I could add;

 

IsNull,0 somehow my query? would I be correct in thinking that if no record is returned for a month, then it would display '0' as in my chart above?

 

By the way, thankyou ever so much for your time in helping me.

[Barand]

Oops!

 

Change WHERE to AND

 

That will make it part of the join condition

 

edit

PS You may want to try

 

IFNULL(AVG( yard ), 0) AS avscore

which will give zero where a non-match would give null

Edited December 8, 2012 by Barand