MYSQL select where count > 1

Question

Need to execute query to find rowns with 3rd column value count > 1. Example:

Table:

     col1   col2    col3  
------------------------  
1.   val1   val2    val3
2.  val11  val21    val3
3.  val12  val22   val31
4.  val13  val23   val32
5.  val14  val24   val32

Result will be rows 1,2,4,5 because we have values in col3 with count > 1

score 10 · Answer 1 · answered Jul 30 '12 at 13:11

10

Your question is very unclear, this is probably what you're asking:

SELECT COUNT(col3) as countCol3
FROM table
GROUP BY col3
HAVING countCol3 > 1

answered Jul 30 '12 at 13:11

Tchoupi

14,560
5
37
71

1

Not the down-voter, but the OP seems to be asking for the rows, not (just) the count. While this query is a hint to getting an answer for the OP, it does not provide a complete answer (at least how I interpret the question, and I may be wrong ^^) – jswolf19 Jul 30 '12 at 13:36
@jswolf19 I see your point. However, as you can imagine I am not willing to give a full detailed answer to someone who does't ask a full detailed question. The question doesn't provide with any primary keys, or nothing that could be used to retrieve the full row in a decent manner. – Tchoupi Jul 30 '12 at 13:40

score 1 · Answer 2 · answered Jul 30 '12 at 15:20

Here is an answer that could help you. I have included some sample data to help others who would find some example data helpful:

drop table if exists valTable;

create table valTable
(
id int unsigned primary key auto_increment not null,
col1 varchar(50),
col2 varchar(50),
col3 varchar(50)
);

insert into valTable (col1,col2,col3) values ('val1','val2','val3');
insert into valTable (col1,col2,col3) values ('val11','val21','val3');
insert into valTable (col1,col2,col3) values ('val12','val22','val31');
insert into valTable (col1,col2,col3) values ('val13','val23','val32');
insert into valTable (col1,col2,col3) values ('val14','val24','val32');

select vt.*
from valTable vt
where vt.col3 in
(select col3Multi from (
select col3 col3Multi,count(*) as c
from valTable
group by col3
having c > 1) t );

MYSQL select where count > 1

2 Answers2