I’m trying to make a leaderboard but only to count the records from a certain year. I have done it like this but not sure if this is correct.
$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 AND YEAR(si.regdate) = $currYear ORDER BY avg_stfpoints DESC;";
It seems like it doesn’t do the work… What do I do wrong… Also, I only want it to count the point_total for that specific year…
It returns some records, but doesnt seem to return all the records from 2011?! I have a group with atleast 10 records from 2011 and they are not on the list… I think there is som error in that part but not sure…
If your question is about the speed or efficiency of you sql statement, then look in your mysql logs and pick up the exact query that was sent to the database.
Copy it.
Put the word DESCRIBE in front of it, and paste the whole thing directly into whatever you use to manage your database directly.
Grab the output of that.
Paste that here along with the full output of DESCRIBE <table-name> for each of your tables.
IF your question is solely about sql, then you are best asking to have this thread moved to the sql or mysql forum on this site.
or, try reading some of these links [google]understanding mysql describe[/google].
Edit:
Oh, I see you posted another reply illuminating your problem, well my advice still stands.
I think I’m gettin closer… Allthough I get an error whern I do 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 WHERE YEAR(si.regdate) = $currYear
GROUP BY cu.clubid
HAVING COUNT(point_total) >= 2 ORDER BY avg_stfpoints DESC;
The error is this: Error: (1066) Not unique table/alias: ‘c’