Re: Returning where COUNT > 5

[Michael Stassen]

You cannot do that in the WHERE clause.  The WHERE clause determines which 
rows to look at.  That is, in this case, it determines which rows to count. 
 How can it choose which rows to count based on the result of the count? 
See the problem?

Instead, you need to use the HAVING clause, which filters at the end (after 
the counting), like this:

  SELECT g.groupsDescr, r.roles_Agroup,
         r.rolesDescr, COUNT(r.rolesDescr) AS TOTAL
  FROM roles r
       INNER JOIN groups_roles gr ON (r.rolesID = gr.fkrolesID)
       INNER JOIN groups g ON (gr.fkgroupsID = g.groupsID)
  GROUP BY r.rolesDescr
  HAVING TOTAL > 5;
Michael
ColdFusion Lists wrote:
Hi all
how to do this in MySQL? Returning only records with COUNT > 5?
SELECT 
  `groups`.`groupsDescr`,
  `roles`.`roles_Agroup`,
  `roles`.`rolesDescr`,
  COUNT(`roles`.`rolesDescr`) AS TOTAL
FROM
  `roles`
  INNER JOIN `groups_roles` ON (`roles`.`rolesID` =
`groups_roles`.`fkrolesID`)
  INNER JOIN `groups` ON (`groups_roles`.`fkgroupsID` =
`groups`.`groupsID`)
GROUP BY `roles`.`rolesDescr`
WHERE COUNT(`roles`.`rolesDescr`) GT 5

MySQL 4.0.12 give an error... maybe because i'm using WHERE COUNT?
Thanx for your time.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]