Opposite of Distinct in MySQL

Question

I would like to know if there is an opposite of "select distinct" in sql ,so that i can use to get values from a table of only which has repeated multiple times.

Thanks

score 38 · Accepted Answer · answered Sep 14 '11 at 05:28

38

select some_column, count(*)
from some_table
group by 1
having count(*) > 1;

On databases like mysql, you may even omit selecting count(*) to leave just the column values:

select some_column
from some_table
group by 1
having count(*) > 1;

answered Sep 14 '11 at 05:28

Bohemian

412,405
93
575
722

3

@bohemian what does the `1` of `group by 1` means, the first column in the select clause? – xdazz Sep 14 '11 at 05:36
5

`group by 1` means "group by column number 1 in the select clause", in this case equivalent to coding `group by some_column`. I prefer the use numbers instead of column names when grouping, especially when the column is a calculation - it saves having to re-code the entire calculation, which can be very hard to read when the expression is long – Bohemian Sep 14 '11 at 13:22
1

@Bohemian, Does "using numbers instead of names" work for other cases or does it only work for `group by` and `order by`? – Pacerier Apr 16 '15 at 03:11
2

@Pacerier it only works for `group by` and `order by`. It's part of the SQL standard. – Bohemian Apr 16 '15 at 04:28
2

@Bohemian Sorry for gravedigging, but for the public record I couldn't leave this without adding that you still can use aliases - e.g. `SELECT SOME(BIG(CALC(my_column+42))) AS calculated FROM [...] GROUP BY calculated` – Nico R Mar 05 '20 at 12:15
@NicoR what relevance does specifying an alias or not have to this question or this answer? – Bohemian Mar 06 '20 at 02:20
1

@Bohemian You don't need to keep track of which number the column is you want to refer to, but only its name, which can come in handy when you change the columns or their order in your SELECT clause. – Nico R Mar 07 '20 at 13:14

paxdiablo · Answer 2 · 2011-09-14T05:33:39.707

You need a group by with a having clause, something like:

select person, count(friends)
from people
group by person
having count(friends) > 1

This would give you a list of all people and their friend count, except for those poor sad soles who have been befriended by only their mothers or, worse yet, not even their mothers :-)

You have to use having instead of where in this case, since the former filters after grouping while the latter filters before, and you don't have the information of aggregate functions like count() until after grouping has taken place.

score 2 · Answer 3 · answered Aug 15 '12 at 12:29

I was looking for something like this for a many-to-many join situation where I want to show how many duplicates exist in both tables. count(distinct col) wasn't exactly the right solution because result of '1' indicated that the column indeed had duplicates, but didn't say how many. The 'opposite of distinct' I used for this was an inversion using count(*): (count(*)-count(distinct col)+1) and it did the job fine.

Opposite of Distinct in MySQL

3 Answers3

Linked