0

I have a table containing two fields: ID and USERNAME. How to my make the SELECT query that will select only rows with minimal two rows grouped?

This is what inside the MySQL table:

ID| Username

1 | peter
2 | jack
3 | peter
4 | ann
5 | peter
6 | jack

I want to select in result like this:

peter (3)
jack (2)

Username 'ann' will be ignored in selection cause it's only one row.

Added: Cannot implement it to my situation. Ok, I will show what I exactly want to do, something like this: (SELECT ip AS ip1 FROM ip_denylist) UNION (SELECT COUNT(user) AS sometotal FROM entrances WHERE ip = ip1 AND HAVING COUNT(sometotal) > 1) GROUP BY user.

But it's not working! Could you show how it must be?

The idea of this select is first: to select each ip address from the ip_denylist table, 2nd: select grouped by user rows from the table entrances that have same ip addresses.

ilnur777
  • 1,135
  • 5
  • 16
  • 28
  • Your question almost identical to [this](http://stackoverflow.com/questions/688549/finding-duplicate-values-in-mysql) post. – phaxian Mar 01 '11 at 22:21
  • Cannot implement it to my situation. Ok, I will show what I exactly want to do, something like this: (SELECT ip AS ipi1 FROM ip_denylist) UNION (SELECT COUNT(user) AS sometotal FROM entrances WHERE ip = ip1 AND HAVING COUNT(sometotal) > 1) GROUP BY user. But it's not working! Could you show how it must be? – ilnur777 Mar 01 '11 at 22:56
  • Do you need something like this? `SELECT user, COUNT(user) AS sometotal FROM entrances WHERE ip in (SELECT ip FROM ip_denylist) GROUP BY user` – criticus Mar 02 '11 at 00:09
  • @criticus I'm trying to make this select: `SELECT ip, COUNT(user) AS sometotal FROM entrances WHERE ip in (SELECT ip FROM ip_denylist WHERE status='1') HAVING COUNT(sometotal) > 2 GROUP BY user` - but it's not working. The idea is: SELECT **IP** from all ip's stored table **IP_DENYLIST** then search more then two users that belong to this **IP** at the **ENTRANCES** table with grouping all found users. Then display it on the webpage like: `IP: 192.0.0.1 has 3 accounts`. This is to find multi-accounts registrations! – ilnur777 Mar 02 '11 at 16:55
  • @ilnur777 your query should work, just add `GROUP BY`. So you'll get `SELECT ip, COUNT(user) AS sometotal FROM entrances WHERE ip in (SELECT ip FROM ip_denylist WHERE status='1') GROUP BY ip HAVING COUNT(sometotal) > 2` – criticus Mar 02 '11 at 17:05
  • @criticus I finally need this one: `SELECT ip FROM ip_denylist WHERE status='1' AND ip in (SELECT COUNT(user) AS sometotal FROM entrances GROUP BY user HAVING COUNT(sometotal) > 2)`. That will mean searching for multiply user accounts that belongs only to one unique ip address. But in this case I can't get the total grouped by user count, at the same time this query hangs up and nothing happens... ((( – ilnur777 Mar 02 '11 at 18:31
  • @ilnur777 I don't understand what is difference between the last query and the previous one. Can you please describe in plain english what you need to get in result? – criticus Mar 02 '11 at 21:21
  • @criticus the last one is not working but the scenario is exactly what I would like the query to be. `first ip_denylist table` contains all ip addresses with some information, not containing the usernames. `second entrances table` contains both ip addresses and usernames. I want to make such select query that will get rows to while operator only ip addresses that have more than two users on it to detect multi accounts. that is my situation. – ilnur777 Mar 03 '11 at 01:04
  • @criticus Can I hope to your help? – ilnur777 Mar 03 '11 at 18:19
  • @ilnur776 the problem in your last query is in `ip in (SELECT COUNT(user)`. Your subquery should return the same type of values that the value you try to compare with. So the correct syntax would be `ip IN (SELECT IP...`. And I still don't understand what logic is incorrect in the query from my second comment – criticus Mar 03 '11 at 18:59
  • @criticus This your query `SELECT ip, COUNT(user) AS sometotal FROM entrances WHERE ip in (SELECT ip FROM ip_denylist WHERE status='1') GROUP BY ip HAVING COUNT(sometotal) > 2` should be vice-versa. Because table **ip_denylist** contains unique (not repeated) ips and table **entrances** contains repeated ips. That's why I need the query that will first take unique ip from **ip_denylist** then being compared with found on the **entrances** table ips. – ilnur777 Mar 03 '11 at 19:15
  • @ilnur777 Subquery works before the main query. First db will select all IPs from `ip_denylist` with `status=1`, then select users from `entrances` who have IPs from the subquery and group resulted rows by IP so you will get unique IPs. – criticus Mar 03 '11 at 19:22
  • @criticus Ok, here is my final SELECT query: `SELECT 'id,'ip','total_q','date_fs','date_ls' FROM ip_denylist WHERE status='1' AND ip in (SELECT 'ip' FROM entrances GROUP BY user HAVING COUNT(ip) > 2`. Did you mention the situation like **entrances** table can have one username with different ips? And with my final query how can I COUNT users now? Sorry that tiring you out, but I really must know how to make this query work! Thank you for your patience! ;-) – ilnur777 Mar 03 '11 at 20:24
  • @ilnur777 once more can you pls explain what do you need to get from your database in plain english, without queries? Users who login from blocked IPs or blocked IPs which have been used by more than 2 accounts? – criticus Mar 03 '11 at 21:12
  • @criticus users who are using more then 2 accounts! :-) – ilnur777 Mar 04 '11 at 14:56
  • @ilnur777 Hopefully this is what you need `SELECT DISTINCT e.user, i.ip, i.date_fs, i.total_q, i.date_ls FROM entrances e WHERE e.ip IN (SELECT e.ip FROM entrances e GROUP BY e.ip HAVING COUNT(DISTINCT e.user)>2) LEFT JOIN ip_denylist i ON (i.ip=e.ip)`. In subquery we select all IPs that were used by more than 2 unique users, then in the main select we choose users on those selected IPs and join information about the IP – criticus Mar 04 '11 at 16:45

2 Answers2

1
SELECT UserName, COUNT(UserName) AS sometotal FROM table
GROUP BY UserName HAVING COUNT(UserName) > 1
Bryan Crosby
  • 6,486
  • 3
  • 36
  • 55
0
select username,count(*) as numb 
from table 
group by username 
having numb > 1
order by numb desc
Nicola Cossu
  • 54,599
  • 15
  • 92
  • 98