List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 20 2006 3:27pm
Subject:Re: where group and inner join
View as plain text  
Peter,
> 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?
>   
Instead of WHERE, try ...

... HAVING SUM(tps.strokes)<90


PB

-----
> Best regards,
> Peter Lauri
>
>
>   


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.4/318 - Release Date: 4/18/2006

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