CODEWITHSUNDEEP
mysql
Vadym
Vadym

Reputation: 548

How to get count from distinct value one column in MYSQL

I have a table like this:

id     2      3

1,   14406,  2189
2,   14428,  2191,
3,   14442,  2192,
4,   14441,  2192,
5,   14441,  2192,

And I wonder how if it's possible to get count so I can tell how many times string where 2nd column and 3rd column are the same appears. I see that 1st string is unique so it appears 1 time. The same with 2. In the third string 2 column is unique so it means this row is unique too. But 4th and 5th string are the same and I want to display that it appears 2 times in my table. How could I count it? Should I use Group by or something like subqueries?

Upvotes: 0

Views: 43

Answers (5)

Barranka
Barranka

Reputation: 21067

Maybe something like this:

select `2`, `3`, count(*)
from (select distinct `2`, `3` from your_table) as a
group by `2`, `3`
having count(*) > 1

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

First you'd group by col 2 and 3, so as to get one record for each distinct pair. For each pair you want all IDs involved, so build a string of IDs with GROUP_CONCAT. Then only keep pairs that occur more than once using the HAVING clause.

select col2, col3, group_concat(id) as ids
from mytable
group by col2, col3
having count(*) > 1;

col2   col3   ids  
14441  2192   4,5

(This is essentially splash58's answer, so you can accept that. I just thought it would be good to explain the steps involved.)

Upvotes: 1

user3600910
user3600910

Reputation: 3109

select 2,count(*)
from my_table
group by
2

Upvotes: 0

splash58
splash58

Reputation: 26153

select group_concat(id), count(*) c 
    from thetable 
  group by `2`, `3` 
  having c > 1

result

group_concat(id)    c
4,5                 2

Upvotes: 0

Nir-Z
Nir-Z

Reputation: 869

Try this:

SELECT count(id) as count_times
FROM table 
WHERE column1=column2

Upvotes: 0

Related Questions