CODEWITHSUNDEEP
mysqlgroup-by
Sean
Sean

Reputation: 1313

Select rows where Count(*) > 1 from a group by clause

So I have this MySQL query that supposed to gather every person in the same tree (your relatives) with the same birthlocationtown, and group and order by the count of those towns, effectively singling out all relatives born together in the same town:

SELECT birthLocationTown, count(*) AS Number
FROM Events as e1, Person as p1, Tree as t1
WHERE e1.fk_person = p1.personID
AND
p1.fk_treeID = 
(SELECT treeID
FROM Tree as t1
WHERE t1.fk_owner = '1')
AND
Number > 1
GROUP BY birthLocationTown
ORDER BY Number

The syntax is correct, except for the Count Number, which I am unsure how to format. I thought that renaming the count as Number, I can specify only the Number > 1, indicating multiple relatives born in the same town.

Upvotes: 1

Views: 196

Answers (1)

spencer7593
spencer7593

Reputation: 108420

The HAVING clause can operate on aggregate expressions. (The WHERE clause can't operate on aggregate expressions from the SELECT list.) To fix the syntax problem in the query (I've not analyzed anything else about the query, the result set that it returns) just ...

replace:

AND Number > 1

with:

HAVING `Number` > 1

or:

HAVING COUNT(*) > 1

Upvotes: 1

Related Questions