List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:July 27 2005 7:35am
Subject:Re: Where on count(*)
View as plain text  
Pupeno

>SELECT `Plans`.`id`, `Plans`.`name`, count(*) as 'count' FROM `Plans` JOIN 
>`TechsPerPlan` ON `Plans`.`id` = `TechsPerPlan`.`plan` WHERE 
>`TechsPerPlan`.`id` IN (17, 48, 54, 64, 75, 13, 30, 37, 45, 55, 65, 76, 11, 
>33, 46, 58, 68, 80) AND count >= 3 GROUP BY `Plans`.`id`

>but it selected only those with less that 3, what I am doing wrong ?

Column aliases and aggregate results like Count() are resolved after the 
Where clause so they can't occur in it. Use Having instead.

PB

-----

Pupeno wrote:

>I have esentially this query (the list of integers may differ):
>
>SELECT `Plans`.`id`, `Plans`.`name`, count(*) as 'count' FROM `Plans` JOIN 
>`TechsPerPlan` ON `Plans`.`id` = `TechsPerPlan`.`plan` WHERE 
>`TechsPerPlan`.`id` IN (17, 48, 54, 64, 75, 13, 30, 37, 45, 55, 65, 76, 11, 
>33, 46, 58, 68, 80) GROUP BY `Plans`.`id`
>
>Of that result I want those with count bigger than N (being N a number, like 
>3), I tried this:
>
>SELECT `Plans`.`id`, `Plans`.`name`, count(*) as 'count' FROM `Plans` JOIN 
>`TechsPerPlan` ON `Plans`.`id` = `TechsPerPlan`.`plan` WHERE 
>`TechsPerPlan`.`id` IN (17, 48, 54, 64, 75, 13, 30, 37, 45, 55, 65, 76, 11, 
>33, 46, 58, 68, 80) AND count >= 3 GROUP BY `Plans`.`id`
>
>but it selected only those with less that 3, what I am doing wrong ?
>
>Thanks
>  
>


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.9.5/58 - Release Date: 7/25/2005

Thread
Where on count(*)Pupeno26 Jul
  • Re: Where on count(*)SGreen26 Jul
  • Re: Where on count(*)mfatene26 Jul
  • Re: Where on count(*)Peter Brawley27 Jul