Re: Returning where COUNT 5

2004-06-21 Thread Daniel Clark 
With a join and group by I think you have HAVING.

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]

Returning where COUNT 5

2004-06-20 Thread ColdFusion Lists 
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.



-
Crie seu Yahoo! Mail, agora com 100MB de espaço, anti-spam e antivírus grátis!

Re: Returning where COUNT 5

2004-06-20 Thread 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]