In the last episode (Apr 20), Peter Lauri said:
> Best groupmember,
>
> I am doing this query that works fine.
>
> SELECT
> tps.tour_player_id,
> sum(if(tsh.hole_number<=9, tps.strokes, '0')) AS 'front_9',
> sum(if(tsh.hole_number>=10, tps.strokes, '0')) AS 'back_9',
> sum(tps.strokes) AS 'score'
> FROM tour_player_score tps
> INNER JOIN tour_scorecard_hole tsh ON tps.scorecard_hole_id=tsh.id
> GROUP BY tps.tour_player_id
> ORDER BY
> score,
> back_9;
>
> However, I would like to just get the result where sum(tps.strokes)<90, so I
> added WHERE sum(tps.strokes)<90 after the inner join like this
>
> SELECT
> tps.tour_player_id,
> sum(if(tsh.hole_number<=9, tps.strokes, '0')) AS 'front_9',
> sum(if(tsh.hole_number>=10, tps.strokes, '0')) AS 'back_9',
> sum(tps.strokes) AS 'score'
> FROM tour_player_score tps
> INNER JOIN tour_scorecard_hole tsh ON tps.scorecard_hole_id=tsh.id
> WHERE sum(tps.strokes) < 90
> GROUP BY tps.tour_player_id
> ORDER BY
> score,
> back_9;
It's useful to note that SELECT statements generally work in the order
they are written. The WHERE clause applies to the records as they are
read from the source tables, and at that point, no grouping has been
done, so there's no sum. Try moving your filter to a HAVING clause,
which comes between GROUP BY and HAVING, and applies to the final
resultset of the table (and at that point, you do have a
sum(tps.strokes) column).
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
