Pupeno <[EMAIL PROTECTED]> wrote on 07/26/2005 03:53:10 PM: > 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 > -- > Pupeno <[EMAIL PROTECTED]> (http://pupeno.com)
WHERE clauses are evaluated *before* the GROUP BY is processed. The GROUP BY processing is where the value of `count` is computed. What you want to do is to place your condition in a HAVING clause like 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)GROUP BY `Plans`.`id` HAVING count >= 3 Shawn Green Database Administrator Unimin Corporation - Spruce Pine
