mysql query to group duplicate records with a different field
up vote
1
down vote
favorite
I have a table T1 with columns id, C1, C2 and C3. I am using the following query to find duplicate records
Select group_concat(id) from T1 group by C2 having count(id) >1;
Now I want to group all the duplicate records by column C3. How do I do it?
Note : I am not expecting
Select group_concat(id) from T1 group by C2,C3 having count(id) >1;
I want to get all the records having duplicate values on C2 and group them only based on C3 irrespective of their C2 value
id C1 C2 C3
1 a 3 A
2 b 2 A
3 c 2 A
4 d 2 B
5 e 3 C
In the above data 1,5 are duplicate record with C2 value 3 and 2,3,4 is duplciate records with C2 value 2. I want an output
A - has 2 duplicates (with C2 values 2 and 3 )
B - has 1 duplicate (with C2 value 2)
C - has 1 duplicate (with C2 value 3)
mysql
asked Nov 12 at 8:16
Jerry
371220
add a comment |
up vote
1
down vote
favorite
I have a table T1 with columns id, C1, C2 and C3. I am using the following query to find duplicate records
Select group_concat(id) from T1 group by C2 having count(id) >1;
Now I want to group all the duplicate records by column C3. How do I do it?
Note : I am not expecting
Select group_concat(id) from T1 group by C2,C3 having count(id) >1;
I want to get all the records having duplicate values on C2 and group them only based on C3 irrespective of their C2 value
id C1 C2 C3
1 a 3 A
2 b 2 A
3 c 2 A
4 d 2 B
5 e 3 C
In the above data 1,5 are duplicate record with C2 value 3 and 2,3,4 is duplciate records with C2 value 2. I want an output
A - has 2 duplicates (with C2 values 2 and 3 )
B - has 1 duplicate (with C2 value 2)
C - has 1 duplicate (with C2 value 3)
mysql
asked Nov 12 at 8:16
Jerry
371220
Help us help you - please share some sample data and the result you're trying to get for it.
– Mureinik
Nov 12 at 8:23
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I have a table T1 with columns id, C1, C2 and C3. I am using the following query to find duplicate records
Select group_concat(id) from T1 group by C2 having count(id) >1;
Now I want to group all the duplicate records by column C3. How do I do it?
Note : I am not expecting
Select group_concat(id) from T1 group by C2,C3 having count(id) >1;
I want to get all the records having duplicate values on C2 and group them only based on C3 irrespective of their C2 value
id C1 C2 C3
1 a 3 A
2 b 2 A
3 c 2 A
4 d 2 B
5 e 3 C
In the above data 1,5 are duplicate record with C2 value 3 and 2,3,4 is duplciate records with C2 value 2. I want an output
A - has 2 duplicates (with C2 values 2 and 3 )
B - has 1 duplicate (with C2 value 2)
C - has 1 duplicate (with C2 value 3)
mysql
asked Nov 12 at 8:16
Jerry
371220
I have a table T1 with columns id, C1, C2 and C3. I am using the following query to find duplicate records
Select group_concat(id) from T1 group by C2 having count(id) >1;
Now I want to group all the duplicate records by column C3. How do I do it?
Note : I am not expecting
Select group_concat(id) from T1 group by C2,C3 having count(id) >1;
I want to get all the records having duplicate values on C2 and group them only based on C3 irrespective of their C2 value
id C1 C2 C3
1 a 3 A
2 b 2 A
3 c 2 A
4 d 2 B
5 e 3 C
In the above data 1,5 are duplicate record with C2 value 3 and 2,3,4 is duplciate records with C2 value 2. I want an output
A - has 2 duplicates (with C2 values 2 and 3 )
B - has 1 duplicate (with C2 value 2)
C - has 1 duplicate (with C2 value 3)
mysql
mysql
asked Nov 12 at 8:16
Jerry
371220
asked Nov 12 at 8:16
Jerry
371220
edited Nov 12 at 8:25
asked Nov 12 at 8:16
Jerry
371220
asked Nov 12 at 8:16
Jerry
371220
asked Nov 12 at 8:16
Jerry
371220
371220
Help us help you - please share some sample data and the result you're trying to get for it.
– Mureinik
Nov 12 at 8:23
add a comment |
Help us help you - please share some sample data and the result you're trying to get for it.
– Mureinik
Nov 12 at 8:23
Help us help you - please share some sample data and the result you're trying to get for it.
– Mureinik
Nov 12 at 8:23
Help us help you - please share some sample data and the result you're trying to get for it.
– Mureinik
Nov 12 at 8:23
add a comment |
2 Answers
2
active
oldest
votes
up vote
1
down vote
accepted
- In a Derived Table, we can
GROUP BYonC2and identify their count(s).C2value having count more than 1 is basically a duplicate (occurring in more than one row). - Join this result-set to the main table on
C2. This will help us in getting an additional column showingC2count against every row. - Now, we can use conditional aggregation on
C3usingCOUNT(DISTINCT ...), considering those cases where count is more than 1.
Try:
SELECT
t.C3,
COUNT(DISTINCT IF(dt.count_C2 > 1, t.C2, NULL)) AS duplicates
FROM
your_table AS t
JOIN
(
SELECT
C2,
COUNT(id) AS count_C2
FROM your_table
GROUP BY C2
) AS dt
ON dt.C2 = t.C2
GROUP BY t.C3
Result
| C3 | duplicates |
| --- | ---------- |
| A | 2 |
| B | 1 |
| C | 1 |
View on DB Fiddle
answered Nov 12 at 8:43
Madhur Bhaiya
19.3k62236
add a comment |
up vote
1
down vote
SELECT GROUP_CONCAT(id)
FROM T1
WHERE C2 IN
(
SELECT C2
FROM T1
GROUP BY C2
HAVING COUNT(id)>1
)
GROUP BY C3
answered Nov 12 at 8:29
sbrbot
2,89112143
what if my first group by is with multiple properties, say Group by C2,C1
– Jerry
Nov 12 at 8:35
@Jerry what if you try and check what happens? If that is a valid use case, you should have mentioned it before
– Nico Haase
Nov 12 at 9:02
I can have multiple where clauses that case, is that an optimal way to achieve this?
– Jerry
Nov 12 at 9:41
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53258136%2fmysql-query-to-group-duplicate-records-with-a-different-field%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
- In a Derived Table, we can
GROUP BYonC2and identify their count(s).C2value having count more than 1 is basically a duplicate (occurring in more than one row). - Join this result-set to the main table on
C2. This will help us in getting an additional column showingC2count against every row. - Now, we can use conditional aggregation on
C3usingCOUNT(DISTINCT ...), considering those cases where count is more than 1.
Try:
SELECT
t.C3,
COUNT(DISTINCT IF(dt.count_C2 > 1, t.C2, NULL)) AS duplicates
FROM
your_table AS t
JOIN
(
SELECT
C2,
COUNT(id) AS count_C2
FROM your_table
GROUP BY C2
) AS dt
ON dt.C2 = t.C2
GROUP BY t.C3
Result
| C3 | duplicates |
| --- | ---------- |
| A | 2 |
| B | 1 |
| C | 1 |
View on DB Fiddle
answered Nov 12 at 8:43
Madhur Bhaiya
19.3k62236
add a comment |
up vote
1
down vote
accepted
- In a Derived Table, we can
GROUP BYonC2and identify their count(s).C2value having count more than 1 is basically a duplicate (occurring in more than one row). - Join this result-set to the main table on
C2. This will help us in getting an additional column showingC2count against every row. - Now, we can use conditional aggregation on
C3usingCOUNT(DISTINCT ...), considering those cases where count is more than 1.
Try:
SELECT
t.C3,
COUNT(DISTINCT IF(dt.count_C2 > 1, t.C2, NULL)) AS duplicates
FROM
your_table AS t
JOIN
(
SELECT
C2,
COUNT(id) AS count_C2
FROM your_table
GROUP BY C2
) AS dt
ON dt.C2 = t.C2
GROUP BY t.C3
Result
| C3 | duplicates |
| --- | ---------- |
| A | 2 |
| B | 1 |
| C | 1 |
View on DB Fiddle
answered Nov 12 at 8:43
Madhur Bhaiya
19.3k62236
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
- In a Derived Table, we can
GROUP BYonC2and identify their count(s).C2value having count more than 1 is basically a duplicate (occurring in more than one row). - Join this result-set to the main table on
C2. This will help us in getting an additional column showingC2count against every row. - Now, we can use conditional aggregation on
C3usingCOUNT(DISTINCT ...), considering those cases where count is more than 1.
Try:
SELECT
t.C3,
COUNT(DISTINCT IF(dt.count_C2 > 1, t.C2, NULL)) AS duplicates
FROM
your_table AS t
JOIN
(
SELECT
C2,
COUNT(id) AS count_C2
FROM your_table
GROUP BY C2
) AS dt
ON dt.C2 = t.C2
GROUP BY t.C3
Result
| C3 | duplicates |
| --- | ---------- |
| A | 2 |
| B | 1 |
| C | 1 |
View on DB Fiddle
answered Nov 12 at 8:43
Madhur Bhaiya
19.3k62236
- In a Derived Table, we can
GROUP BYonC2and identify their count(s).C2value having count more than 1 is basically a duplicate (occurring in more than one row). - Join this result-set to the main table on
C2. This will help us in getting an additional column showingC2count against every row. - Now, we can use conditional aggregation on
C3usingCOUNT(DISTINCT ...), considering those cases where count is more than 1.
Try:
SELECT
t.C3,
COUNT(DISTINCT IF(dt.count_C2 > 1, t.C2, NULL)) AS duplicates
FROM
your_table AS t
JOIN
(
SELECT
C2,
COUNT(id) AS count_C2
FROM your_table
GROUP BY C2
) AS dt
ON dt.C2 = t.C2
GROUP BY t.C3
Result
| C3 | duplicates |
| --- | ---------- |
| A | 2 |
| B | 1 |
| C | 1 |
View on DB Fiddle
answered Nov 12 at 8:43
Madhur Bhaiya
19.3k62236
edited Nov 12 at 8:59
answered Nov 12 at 8:43
Madhur Bhaiya
19.3k62236
answered Nov 12 at 8:43
Madhur Bhaiya
19.3k62236
answered Nov 12 at 8:43
Madhur Bhaiya
19.3k62236
19.3k62236
add a comment |
add a comment |
up vote
1
down vote
SELECT GROUP_CONCAT(id)
FROM T1
WHERE C2 IN
(
SELECT C2
FROM T1
GROUP BY C2
HAVING COUNT(id)>1
)
GROUP BY C3
answered Nov 12 at 8:29
sbrbot
2,89112143
what if my first group by is with multiple properties, say Group by C2,C1
– Jerry
Nov 12 at 8:35
@Jerry what if you try and check what happens? If that is a valid use case, you should have mentioned it before
– Nico Haase
Nov 12 at 9:02
I can have multiple where clauses that case, is that an optimal way to achieve this?
– Jerry
Nov 12 at 9:41
add a comment |
up vote
1
down vote
SELECT GROUP_CONCAT(id)
FROM T1
WHERE C2 IN
(
SELECT C2
FROM T1
GROUP BY C2
HAVING COUNT(id)>1
)
GROUP BY C3
answered Nov 12 at 8:29
sbrbot
2,89112143
what if my first group by is with multiple properties, say Group by C2,C1
– Jerry
Nov 12 at 8:35
@Jerry what if you try and check what happens? If that is a valid use case, you should have mentioned it before
– Nico Haase
Nov 12 at 9:02
I can have multiple where clauses that case, is that an optimal way to achieve this?
– Jerry
Nov 12 at 9:41
add a comment |
up vote
1
down vote
up vote
1
down vote
SELECT GROUP_CONCAT(id)
FROM T1
WHERE C2 IN
(
SELECT C2
FROM T1
GROUP BY C2
HAVING COUNT(id)>1
)
GROUP BY C3
answered Nov 12 at 8:29
sbrbot
2,89112143
SELECT GROUP_CONCAT(id)
FROM T1
WHERE C2 IN
(
SELECT C2
FROM T1
GROUP BY C2
HAVING COUNT(id)>1
)
GROUP BY C3
answered Nov 12 at 8:29
sbrbot
2,89112143
answered Nov 12 at 8:29
sbrbot
2,89112143
answered Nov 12 at 8:29
sbrbot
2,89112143
answered Nov 12 at 8:29
sbrbot
2,89112143
2,89112143
what if my first group by is with multiple properties, say Group by C2,C1
– Jerry
Nov 12 at 8:35
@Jerry what if you try and check what happens? If that is a valid use case, you should have mentioned it before
– Nico Haase
Nov 12 at 9:02
I can have multiple where clauses that case, is that an optimal way to achieve this?
– Jerry
Nov 12 at 9:41
add a comment |
what if my first group by is with multiple properties, say Group by C2,C1
– Jerry
Nov 12 at 8:35
@Jerry what if you try and check what happens? If that is a valid use case, you should have mentioned it before
– Nico Haase
Nov 12 at 9:02
I can have multiple where clauses that case, is that an optimal way to achieve this?
– Jerry
Nov 12 at 9:41
what if my first group by is with multiple properties, say Group by C2,C1
– Jerry
Nov 12 at 8:35
what if my first group by is with multiple properties, say Group by C2,C1
– Jerry
Nov 12 at 8:35
@Jerry what if you try and check what happens? If that is a valid use case, you should have mentioned it before
– Nico Haase
Nov 12 at 9:02
@Jerry what if you try and check what happens? If that is a valid use case, you should have mentioned it before
– Nico Haase
Nov 12 at 9:02
I can have multiple where clauses that case, is that an optimal way to achieve this?
– Jerry
Nov 12 at 9:41
I can have multiple where clauses that case, is that an optimal way to achieve this?
– Jerry
Nov 12 at 9:41
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53258136%2fmysql-query-to-group-duplicate-records-with-a-different-field%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Help us help you - please share some sample data and the result you're trying to get for it.
– Mureinik
Nov 12 at 8:23