where group and inner join

[Peter Lauri]

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 gives me error 1111: Invalid use of group function

Where does the error come from? And how would I solve this?

Best regards,
Peter Lauri


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]