October 11th, 2000, 10:57 AM
i would like to count a column and only display records where the count is greater than 1
i tried something like:
SELECT COUNT(foo) AS bar
WHERE bar > 1
but that didn't work
October 11th, 2000, 01:04 PM
I don't get what you are saying. count() returns a count of records. you can't use it to reference a record since it's not related to any one record.
October 11th, 2000, 01:35 PM
****, i don't get what i was saying either
i should have said that i'm trying to group records by how many duplicates there are and i only want to see records that have more than one occurance
so i tried to use something like:
SELECT Name, COUNT(Name) AS foo
WHERE foo > 1
GROUP BY Name
but that didn't work, i assume that you just can't use aggregates in WHEREs
any known workaround?
hope that clears it up, sorry for the confusion
October 11th, 2000, 04:54 PM
You'd have to use the count(*) in the where and I'm not even sure if that would work:
select name from tbl where count(*)>1 group by name;
give it a try. If you have a unique identifier you could join the table to itself:
select t1.name,t1.id,t2id from tbl t1,tbl t2 where t1.name=t2.name and t1.id!=t2.id;
October 11th, 2000, 06:34 PM
i get a "ERROR 1111: Invalid use of group function" whenever i try to use an aggregate function in a WHERE
but joining the table on itself worked great, thanks for the idea
Found the solution
The answer is simple. you don't use WHERE, you use HAVING
Originally Posted by RyanP
SELECT COUNT(foo) AS bar
GROUP BY foo
HAVING COUNT(foo) > 1
That was asked FOURTEEN years ago ...!
The moon on the one hand, the dawn on the other:
The moon is my sister, the dawn is my brother.
The moon on my left and the dawn on my right.
My brother, good morning: my sister, good night.
-- Hilaire Belloc
No, i answered because i needed the solution and the first result i found was this feed. If others need the solution, they will come here and the answer thus is here.
Originally Posted by r937
no, i responded because when i needed the answer to this, the first site i visited was this feed. now that the answer is here, people will find it when they come here.
there's only one problem -- the answer you posted is crap
Originally Posted by MichelleKIBA
think about it -- your "solution" returns a column of counts without any indication of what those counts are counting
what possible use could that be?
if you want a hint as to where the real solution lies, go back and read post #3
I wouldn't say the answer is "crap". I'd simply say it's incomplete. It's just missing the 1 field needed to identify the what's being counted.
mysql> select map, count(map) as count from records group by map having count(map) > 20;
| map | count |
| map107 | 21 |
| map155 | 25 |
| map158 | 22 |
| map192 | 26 |
| map230 | 25 |
| map256 | 24 |
| map273 | 22 |
| map59 | 21 |
| map68 | 22 |
| map71 | 23 |
10 rows in set (0.08 sec)
Last edited by FishMonger; June 2nd, 2014 at 10:07 AM.