Getting records from a specific year?

jmansa

I'm trying to make a list of records from differnet groups and that works just fine, but now I only want to get the rcords from a specific year... How do I go about that?

$currYear = 2011;

$sql = "SELECT cu.clubid,AVG(point_total) AS avg_stfpoints, COUNT(point_total) AS counted, c.clubname, c.ccountry, c.seoname, si.regdate FROM ".$prefix."_score_info si 
	INNER JOIN ".$prefix."_club_users cu ON cu.new_userid = si.user 
	INNER JOIN ".$prefix."_club c ON c.clubid = cu.clubid
GROUP BY cu.clubid 
	HAVING COUNT(point_total) >= 10 ORDER BY avg_stfpoints DESC;";

I have a date/time field in the score_info table.

Hoping for help and thanks in advance...

bradgrafelman

Add a WHERE condition to only SELECT those rows where the datetime column has a value in the specified year. Note that you can use MySQL's YEAR() function to quickly extract the year from such a column.

jmansa

When I set it up like this:

SELECT cu.clubid,AVG(point_total) AS avg_stfpoints, COUNT(point_total) AS counted, c.clubname, c.ccountry, c.seoname, si.regdate FROM ".$prefix."_score_info si 
    INNER JOIN ".$prefix."_club_users cu ON cu.new_userid = si.user 
    INNER JOIN ".$prefix."_club c ON c.clubid = cu.clubid
GROUP BY cu.clubid WHERE YEAR(si.regdate) = $currYear 
	HAVING COUNT(point_total) >= 2 ORDER BY avg_stfpoints DESC;

... I get an error:
Error: (1064) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE YEAR(si.regdate) = 2011 HAVING COUNT(point_total) >= 2 ORDER BY avg_stfpoi'

I cant seem to get this right... Please help!

dagon

the WHERE has to come before the GROUP BY

jmansa

dagon;10978693 wrote:
the WHERE has to come before the GROUP BY

Have allmost tryid everything but with no luck... Where to put "WHERE":

SELECT cu.clubid,AVG(point_total) AS avg_stfpoints, COUNT(point_total) AS counted, c.clubname, c.ccountry, c.seoname, si.regdate FROM ".$prefix."_score_info si 
    INNER JOIN ".$prefix."_club_users cu ON cu.new_userid = si.user 
    INNER JOIN ".$prefix."_club c ON c.clubid = cu.clubid WHERE YEAR(si.regdate) = $currYear 
GROUP BY cu.clubid 
	HAVING COUNT(point_total) >= 2 ORDER BY avg_stfpoints DESC;

This gives me this error:
Error: (1066) Not unique table/alias: 'c'

dagon

well the error is nothing to do with the where, as it says c is not a unique table\alias

jmansa

dagon;10978699 wrote:
well the error is nothing to do with the where, as it says c is not a unique table\alias

But if I remove the "WHERE" the error disappears?