List:General Discussion« Previous MessageNext Message »
From:Peter Lauri Date:April 20 2006 4:32am
Subject:where group and inner join
View as plain text  
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

Thread
where group and inner joinPeter Lauri20 Apr
Re: where group and inner joinDan Nelson20 Apr
  • Re: where group and inner joinDan Nelson20 Apr
    • RE: where group and inner joinPeter Lauri20 Apr
Re: where group and inner joinPeter Brawley20 Apr