MySQL query to show duplicates in two columns

Question

I need to count number of rows which have in column A duplicated records and column B must be 1 and have also to be duplicated.

SELECT COUNT(*) as num
FROM myTable
WHERE (columnB = 1 GROUP BY columnB HAVING COUNT(id_myTable) > 1) AND (columnA IN (SELECT columnA FROM myTable GROUP BY columnA HAVING COUNT(id_myTable) > 1))
ORDER BY columnC ASC`

Suppose you have these table named myTable in MySQL:

/---------------------------------/
|      id    | ColumnA | Column B |
|---------------------------------|
|          1 | Andre   |        1 |
|          2 | Joao    |        2 |
|          3 | Maria   |        1 |
|          4 | Joao    |        1 |
|          5 | Andre   |        1 |
|          6 | Maria   |        1 |
|          7 | Andre   |        2 |
/---------------------------------/

The result must be 4, because only id 1, 3, 5 and 6 has both columns duplicated and the condition for columnB must be always equal to 1.

My PHP code to give result:

$query = "select sum(c) from (select count(1) as c from myTable group by columnA, columnB having count(1) > 1 and columnB = 1) t"; $total_pages = mysql_fetch_array(mysql_query($query)); $total_pages = $total_pages[c]; $total_rows = $total_pages;

Your query is not syntactically correct. You should provide sample data and desired results. — Gordon Linoff
– Gordon Linoff, Commented Oct 21, 2017 at 15:27

jophab · Accepted Answer · 2017-10-22 06:40:49Z

1

select sum(c) from (
                     select count(1) as c from myTable  
                     group by ColumnA, ColumnB 
                     having count(1) > 1 and ColumnB=1
                    ) t

edited Oct 22, 2017 at 6:40

answered Oct 21, 2017 at 15:58

jophab

5,54714 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

MSimoes Over a year ago

Thanks @jophab for your reply, it worked. It gives the expected result.

MSimoes Over a year ago

I've found a problem, I've edited my question with 2 new lines in the table. In my db I've changed the status (columnB) for id 6, for example, and the result must be 4, and with your query it gives me 2.

jophab Over a year ago

@MSimoes I have updated my answer.` t` at end of query is an alias for the result obtained from inner query. Some alias name is a must .

MSimoes Over a year ago

thanks again. I've edited again the question because the query is in a PHP and when I use your query on PHP it gives me nothing and when I change sum(c) by * it gives me the result 2 instead of 4. Can you help me.

MSimoes Over a year ago

Finally I've see what I'm doing wrong on 3rd line of PHP. I changed the [c] for ['sum(c)'] and it worked. Thanks to jophab and @GordonLinoff

Gordon Linoff · Accepted Answer · 2017-10-21 22:51:22Z

1

I believe you want:

select sum(cnt)
from (select columnA, columnB, count(*) as cnt
      from myTable
      where columnB = 1
      group by columnA, columnB
      having count(*) > 1  -- or do you mean `count(*) = 2`?
     ) ab;

edited Oct 21, 2017 at 22:51

answered Oct 21, 2017 at 19:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

MSimoes Over a year ago

Thanks @GordonLinoff. I mean that > 1 not equal to 2, and I need that columnB always be equal to 1, because is a status active. By the way I didn't understand ab; at the end of the query.

MSimoes Over a year ago

I've notice you have changed your reply, thanks, I think my problem is the same as with jophab's code. You can see my reply to him.

Collectives™ on Stack Overflow

MySQL query to show duplicates in two columns

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related