Pulling a range in complex query

[rprincejr]

Im trying to pull a range of teams with points from 175-249. I have the query right and it works without trying to get the range. Once I add the range, using between, it breaks. Also I get the error that points isnt a known column. I know its not as Ive assigned it an alias since its a sum of multiple rows. Heres my code and any help is appreciated.

$query = mysql_query("SELECT t.team_id,
                t.partner1_firstname, t.partner1_lastname,
                t.partner2_firstname, t.partner2_lastname,
                s.sched_id, s.date_y, r.div_id,
                SUM(r.results_numfish) AS numfish,
                SUM(r.results_netwgt) AS netwgt,
                MAX(r.results_bigfish) AS bigfish,
                SUM(r.results_points) AS points
                FROM ".$prefix."_tourn_teams t
                LEFT JOIN ".$prefix."_tourn_results r ON r.team_id=t.team_id
                LEFT JOIN ".$prefix."_tourn_schedules s ON s.sched_id=r.sched_id
                WHERE s.date_y=$year AND (points BETWEEN 175 AND 249)
                GROUP BY t.team_id
                ORDER BY points DESC");

[Piranha]

When doing a query it will first evaluage the FROM, JOIN and then the WHERE part. SELECT is not evaluated until after that (don't know exactly when). Basically it means that you can't define a variable there and use in the WHERE-part of the query. Instead you have to do the math there as well:

WHERE s.date_y=$year AND (SUM(r.results_points) BETWEEN 175 AND 249)

[Roger_Ramjet]

More or less right Piranha, more or less. What actually happens is that the WHERE clause is applied to limit the resultset before the GROUP BY clause and any dependant Aggregate Functions can operate.

Makes sense as you don't want to SUM everything in the table if you want to limit it by a condition first: eg SUM all March invoices will give a very different value to SUM all invoices.

What you have to do in this case is to use a sub-query to get all the points for all the teams and then select from that in the outer query for your BETWEEN condition.

[Piranha]

Ty for correcting me RogerRamjet. I didn't even see the Group by, just found one error and thought it was as easy as that. Once again I have missed something because I didn't look through it all, when will I learn? 😛