From: Peter Brawley
Date: April 20 2006 3:27pm
Subject: Re: where group and inner join
List-Archive: http://lists.mysql.com/mysql/197084
Message-Id: <4447A861.7000103@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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