{"id":3564,"date":"2024-08-19T14:51:11","date_gmt":"2024-08-19T06:51:11","guid":{"rendered":"https:\/\/five.co\/?p=3564"},"modified":"2025-04-15T16:31:23","modified_gmt":"2025-04-15T08:31:23","slug":"count-duplicates-in-mysql","status":"publish","type":"post","link":"https:\/\/preview-php85.dzine.org\/five\/blog\/count-duplicates-in-mysql\/","title":{"rendered":"Count Duplicates in MySQL (How to Guide)"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">When working with databases, it&#8217;s common to encounter situations where you need to identify and count duplicate records. In MySQL, detecting duplicates can help in data cleansing, auditing, and ensuring data integrity. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Whether you\u2019re managing customer records, sales transactions, or any other dataset, counting duplicates can provide valuable insights. In this article, we&#8217;ll explore how to count duplicates in MySQL, including different approaches and practical examples.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<div style=\"background-color: #001524;\"><hr style=\"height: 5px;\" \/>\n<pre style=\"text-align: center; overflow: hidden; white-space: pre-line;\"><span style=\"color: #f1ebda; background-color: #4588d8; font-size: calc(25px + 0.390625vw);\"><strong>SQL Made Simple - Try Five<\/strong><br \/><span style=\"font-size: 14pt;\">Five is the Easiest Way to Create, Model and Query a SQL Database<\/span><\/span><\/pre>\n<p style=\"text-align: center;\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\" target=\"_blank\" rel=\"noopener\"><button style=\"background-color: #f8b92b; border: none; color: black; padding: 20px; text-align: center; text-decoration: none; display: inline-block; font-size: 18px; cursor: pointer; margin: 4px 2px; border-radius: 5px;\"><strong>Get Started<\/strong><\/button><\/a><br \/><span style=\"color: #ffffff; font-size: revert;\">___<\/span><\/p>\n<p style=\"text-align: center;\"><span style=\"text-decoration: underline;\"><strong><span style=\"color: #ffffff; text-decoration: underline;\">Here&#8217;s What You Can Do With Five<\/span><\/strong><\/span><\/p>\n<p style=\"text-align: center;\"><span style=\"color: #ffffff;\">Create and Model a SQL Database \u2705<br \/>Write or Build SQL Queries \u2705<br \/>Visualize Queries as Charts or in Dashboards \u2705<br \/>Add CRUD Permissions to Control Data Access \u2705<br \/>Host Your Database Online \u2705\u00a0<\/span><\/p>\n<p style=\"text-align: center;\"><span style=\"color: #f1ebda; background-color: #4588d8;\"><em><strong>&#8220;Five bridges the gap between SQL and the web, <br \/>allowing me to create full-stack applications almost entirely in SQL&#8221;<\/strong><\/em><\/span><br \/><span style=\"color: #ffffff;\">&#8211; Crag Jones, Database Administrator (DBA)<\/span><\/p>\n<hr style=\"height: 5px;\" \/><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n<div class=\"root-eb-toc-q6y8w wp-block-essential-blocks-table-of-contents\"><div class=\"eb-parent-wrapper eb-parent-eb-toc-q6y8w \"><div class=\"eb-toc-container eb-toc-q6y8w  eb-toc-is-not-sticky eb-toc-not-collapsible eb-toc-initially-not-collapsed eb-toc-scrollToTop style-1 list-style-none\" data-scroll-top=\"false\" data-scroll-top-icon=\"fas fa-angle-up\" data-collapsible=\"false\" data-sticky-hide-mobile=\"false\" data-sticky=\"false\" data-scroll-target=\"scroll_to_toc\" data-copy-link=\"false\" data-editor-type=\"\" data-hide-desktop=\"false\" data-hide-tab=\"false\" data-hide-mobile=\"false\" data-itemcollapsed=\"false\" data-highlight-scroll=\"false\"><div class=\"eb-toc-header\"><h2 class=\"eb-toc-title\">Table of Contents<\/h2><\/div><div class=\"eb-toc-wrapper \" data-headers=\"[{&quot;level&quot;:2,&quot;content&quot;:&quot;Why Count Duplicates in MySQL?&quot;,&quot;text&quot;:&quot;Why Count Duplicates in MySQL?&quot;,&quot;link&quot;:&quot;why-count-duplicates-in-mysql&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Counting Duplicates with GROUP BY&quot;,&quot;text&quot;:&quot;Counting Duplicates with GROUP BY&quot;,&quot;link&quot;:&quot;counting-duplicates-with-group-by&quot;},{&quot;level&quot;:4,&quot;content&quot;:&quot;Counting Duplicates Across Multiple Columns&quot;,&quot;text&quot;:&quot;Counting Duplicates Across Multiple Columns&quot;,&quot;link&quot;:&quot;counting-duplicates-across-multiple-columns&quot;},{&quot;level&quot;:4,&quot;content&quot;:&quot;Using DISTINCT with COUNT()&quot;,&quot;text&quot;:&quot;Using DISTINCT with COUNT()&quot;,&quot;link&quot;:&quot;using-distinct-with-count&quot;},{&quot;level&quot;:4,&quot;content&quot;:&quot;Removing Duplicates After Counting&quot;,&quot;text&quot;:&quot;Removing Duplicates After Counting&quot;,&quot;link&quot;:&quot;removing-duplicates-after-counting&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Practical Use Cases for Counting Duplicates&quot;,&quot;text&quot;:&quot;Practical Use Cases for Counting Duplicates&quot;,&quot;link&quot;:&quot;practical-use-cases-for-counting-duplicates&quot;},{&quot;level&quot;:4,&quot;content&quot;:&quot;Frequently Asked Questions (FAQ)&quot;,&quot;text&quot;:&quot;Frequently Asked Questions (FAQ)&quot;,&quot;link&quot;:&quot;frequently-asked-questions-faq&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Summary: Count Duplicates in MySQL&quot;,&quot;text&quot;:&quot;Summary: Count Duplicates in MySQL&quot;,&quot;link&quot;:&quot;summary-count-duplicates-in-mysql&quot;}]\" data-visible=\"[true,true,true,true,true,true]\" data-delete-headers=\"[{&quot;label&quot;:&quot;Why Count Duplicates in MySQL?&quot;,&quot;value&quot;:&quot;why-count-duplicates-in-mysql&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Counting Duplicates with GROUP BY&quot;,&quot;value&quot;:&quot;counting-duplicates-with-group-by&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Counting Duplicates Across Multiple Columns&quot;,&quot;value&quot;:&quot;counting-duplicates-across-multiple-columns&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Using DISTINCT with COUNT()&quot;,&quot;value&quot;:&quot;using-distinct-with-count&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Removing Duplicates After Counting&quot;,&quot;value&quot;:&quot;removing-duplicates-after-counting&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Practical Use Cases for Counting Duplicates&quot;,&quot;value&quot;:&quot;practical-use-cases-for-counting-duplicates&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Frequently Asked Questions (FAQ)&quot;,&quot;value&quot;:&quot;frequently-asked-questions-faq&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Summary: Count Duplicates in MySQL&quot;,&quot;value&quot;:&quot;summary-count-duplicates-in-mysql&quot;,&quot;isDelete&quot;:true}]\" data-smooth=\"true\" data-top-offset=\"\"><div class=\"eb-toc__list-wrap\"><ul class=\"eb-toc__list\"><li><a href=\"#why-count-duplicates-in-mysql\">Why Count Duplicates in MySQL?<\/a><li><a href=\"#counting-duplicates-with-group-by\">Counting Duplicates with GROUP BY<\/a><ul class=\"eb-toc__list\"><li><a href=\"#counting-duplicates-across-multiple-columns\">Counting Duplicates Across Multiple Columns<\/a><li><a href=\"#using-distinct-with-count\">Using DISTINCT with COUNT()<\/a><li><a href=\"#removing-duplicates-after-counting\">Removing Duplicates After Counting<\/a><\/li><\/ul><li><a href=\"#practical-use-cases-for-counting-duplicates\">Practical Use Cases for Counting Duplicates<\/a><ul class=\"eb-toc__list\"><li><a href=\"#frequently-asked-questions-faq\">Frequently Asked Questions (FAQ)<\/a><\/li><\/ul><\/ul><\/div><\/div><\/div><\/div><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Why Count Duplicates in MySQL?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Counting duplicates is essential for several reasons:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Data Integrity<\/strong>: Duplicate records can lead to inaccurate reporting and skewed results, making it important to identify and address them.<\/li>\n\n\n\n<li><strong>Data Cleansing<\/strong>: Removing duplicates helps maintain a clean and reliable database, improving performance and accuracy.<\/li>\n\n\n\n<li><strong>Business Insights<\/strong>: Understanding how often duplicates occur can reveal patterns and help refine processes, such as data entry or merging records from different sources.<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Counting Duplicates with <code>GROUP BY<\/code><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The most straightforward method to count duplicates in MySQL is by using the <code>GROUP BY<\/code> clause. This allows you to group records with the same value(s) and count how often they occur. Here\u2019s a basic example:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"SELECT column_name, COUNT(*) AS duplicate_count\nFROM table_name\nGROUP BY column_name\nHAVING COUNT(*) &gt; 1;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> column_name, <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> duplicate_count<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> table_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> column_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">HAVING<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">In this query:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>column_name<\/code> is the column you want to check for duplicates.<\/li>\n\n\n\n<li><code>COUNT(*)<\/code> counts the number of occurrences for each group of records.<\/li>\n\n\n\n<li>The <code>HAVING COUNT(*) &gt; 1<\/code> clause filters out groups that don&#8217;t have duplicates (i.e., groups with only one occurrence).<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Example<\/strong>:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Suppose you have a table called <code>customers<\/code> with a column <code>email<\/code>. To count duplicate emails, you would use the following query:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"SELECT email, COUNT(*) AS duplicate_count\nFROM customers\nGROUP BY email\nHAVING COUNT(*) &gt; 1;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> email, <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> duplicate_count<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> customers<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> email<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">HAVING<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query will return a list of email addresses that appear more than once in the <code>customers<\/code> table, along with the count of how many times they appear.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Counting Duplicates Across Multiple Columns<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Sometimes, you may need to check for duplicates based on more than one column. For example, you might want to find duplicate customer records based on both <code>first_name<\/code> and <code>last_name<\/code>. In this case, you can modify the query as follows:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"SELECT first_name, last_name, COUNT(*) AS duplicate_count\nFROM customers\nGROUP BY first_name, last_name\nHAVING COUNT(*) &gt; 1;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> first_name, last_name, <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> duplicate_count<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> customers<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> first_name, last_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">HAVING<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query groups records by both <code>first_name<\/code> and <code>last_name<\/code>, counting how many times each combination appears.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Using <code>DISTINCT<\/code> with <code>COUNT()<\/code><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">In some cases, you may want to count distinct occurrences of duplicates. For example, if you have duplicate records but only want to know how many unique duplicates exist, you can combine <code>DISTINCT<\/code> with <code>COUNT()<\/code>:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"SELECT COUNT(DISTINCT column_name) AS unique_duplicates\nFROM table_name\nWHERE column_name IN (\n    SELECT column_name\n    FROM table_name\n    GROUP BY column_name\n    HAVING COUNT(*) &gt; 1\n);\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">DISTINCT<\/span><span style=\"color: #ABB2BF\"> column_name) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> unique_duplicates<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> table_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> column_name <\/span><span style=\"color: #C678DD\">IN<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> column_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> table_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> column_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">HAVING<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query counts the number of unique duplicate values in the specified column.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Removing Duplicates After Counting<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Once you&#8217;ve identified duplicates, you may want to remove them from your table. MySQL provides several ways to delete duplicates, such as using <code>DELETE<\/code> with a <code>JOIN<\/code> or a <code>ROW_NUMBER()<\/code> function. Here&#8217;s a simple example of deleting duplicate rows based on the <code>email<\/code> column:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"DELETE t1\nFROM customers t1\nINNER JOIN (\n    SELECT email, MIN(id) AS min_id\n    FROM customers\n    GROUP BY email\n    HAVING COUNT(*) &gt; 1\n) t2 ON t1.email = t2.email AND t1.id &gt; t2.min_id;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">DELETE<\/span><span style=\"color: #ABB2BF\"> t1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> customers t1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">INNER JOIN<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> email, <\/span><span style=\"color: #56B6C2\">MIN<\/span><span style=\"color: #ABB2BF\">(id) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> min_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> customers<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> email<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">HAVING<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">) t2 <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">t1<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">email<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">t2<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">email<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">t1<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">id<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">t2<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">min_id<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query deletes duplicate records while keeping the first occurrence of each duplicate based on the <code>id<\/code> column.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"has-text-align-center wp-block-paragraph\"><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-cyan-blue-color\"><strong style=\"background-color: rgb(69, 136, 216); color: rgb(241, 235, 218); font-size: calc(12px + 0.390625vw); font-weight: bold;\">Count Duplicates with a Rapid Database Builder<\/strong><\/mark><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">While understanding the basics of SQL and executing queries isn&#8217;t too difficult, building a complete database requires significant SQL knowledge. This is where rapid database builders like Five come into play.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In Five, you can utilise MySQL&#8217;s capabilities, including counting duplicates within your data. Five provides a MySQL database for your application and generates an automatic UI, making it easier to interact with your data and detect any duplicates.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">With Five, you can create interactive forms, dynamic charts and comprehensive reports that are automatically generated based on your database schema. This means you can efficiently implement and visualize the results of your queries, allowing you to identify and manage duplicate records.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Five also enables you to write custom JavaScript and TypeScript functions, providing additional flexibility to implement complex business logic that may involve detecting and handling duplicates. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This is particularly useful for applications that go beyond standard CRUD (Create, Read, Update, Delete) operations, allowing you to automate and optimize your database interactions.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Once your application is ready, Five simplifies deployment with just a few clicks, allowing you to deploy your MySQL-based application to a secure, scalable cloud infrastructure. This lets you focus on development while Five handles the intricacies of cloud deployment.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you&#8217;re serious about building with MySQL, give Five a try. <br>\u25b6\ufe0f <a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\">Sign up for free access to Five&#8217;s online development environment<\/a> and start building your web application today.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\/\" target=\"_blank\" rel=\"noreferrer noopener\"><img src=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/08\/Untitled-design-12-1024x576.gif\" alt=\"A MySQL database application\"\/><\/a><figcaption class=\"wp-element-caption\"><em><strong>An example application built on a MySQL database using Five<\/strong><\/em><\/figcaption><\/figure>\n<\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"wp-block-paragraph\">Now let&#8217;s jump back into counting duplicates using MySQL.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full is-resized\"><img width=\"732\" height=\"1018\" src=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/08\/Screenshot-2024-08-19-at-2.44.11-pm.png\" alt=\"Count Duplicates in MySQL diagram\" class=\"wp-image-3565\" style=\"width:372px;height:auto\" srcset=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/08\/Screenshot-2024-08-19-at-2.44.11-pm.png 732w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/08\/Screenshot-2024-08-19-at-2.44.11-pm-216x300.png 216w\" sizes=\"(max-width: 732px) 100vw, 732px\" \/><\/figure>\n<\/div>\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">The diagram above illustrates the counting duplicates process in MySQL:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>We start with the original table.<\/li>\n\n\n\n<li>We group by the column we&#8217;re checking for duplicates.<\/li>\n\n\n\n<li>We count the occurrences of each value.<\/li>\n\n\n\n<li>We filter for counts greater than 1 to identify duplicates.<\/li>\n\n\n\n<li>The final result gives us the count of duplicates.<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"wp-block-paragraph\">Here&#8217;s a sample table to walk you through the process of counting duplicates using MySQL.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"-- Create a sample table\nCREATE TABLE employees (\n    id INT AUTO_INCREMENT PRIMARY KEY,\n    first_name VARCHAR(50),\n    last_name VARCHAR(50),\n    department VARCHAR(50)\n);\n\n-- Insert sample data\nINSERT INTO employees (first_name, last_name, department) VALUES\n('John', 'Doe', 'IT'),\n('Jane', 'Smith', 'HR'),\n('Mike', 'Johnson', 'IT'),\n('Sarah', 'Williams', 'Marketing'),\n('John', 'Doe', 'Sales'),\n('Emily', 'Brown', 'HR'),\n('John', 'Doe', 'IT');\n\n-- Count duplicates based on first_name and last_name\nSELECT first_name, last_name, COUNT(*) as count\nFROM employees\nGROUP BY first_name, last_name\nHAVING COUNT(*) &gt; 1;\n\n-- Get the total number of duplicate rows\nSELECT COUNT(*) as total_duplicates\nFROM (\n    SELECT first_name, last_name\n    FROM employees\n    GROUP BY first_name, last_name\n    HAVING COUNT(*) &gt; 1\n) as subquery;\n\n-- Bonus: Get all duplicate records\nSELECT e.*\nFROM employees e\nINNER JOIN (\n    SELECT first_name, last_name\n    FROM employees\n    GROUP BY first_name, last_name\n    HAVING COUNT(*) &gt; 1\n) dup ON e.first_name = dup.first_name AND e.last_name = dup.last_name\nORDER BY e.first_name, e.last_name;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Create a sample table<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TABLE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #61AFEF\">employees<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    id <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\"> AUTO_INCREMENT <\/span><span style=\"color: #C678DD\">PRIMARY KEY<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    first_name <\/span><span style=\"color: #C678DD\">VARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">50<\/span><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    last_name <\/span><span style=\"color: #C678DD\">VARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">50<\/span><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    department <\/span><span style=\"color: #C678DD\">VARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">50<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Insert sample data<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">INSERT INTO<\/span><span style=\"color: #ABB2BF\"> employees (first_name, last_name, department) <\/span><span style=\"color: #C678DD\">VALUES<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #98C379\">&#39;John&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;Doe&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;IT&#39;<\/span><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #98C379\">&#39;Jane&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;Smith&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;HR&#39;<\/span><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #98C379\">&#39;Mike&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;Johnson&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;IT&#39;<\/span><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #98C379\">&#39;Sarah&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;Williams&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;Marketing&#39;<\/span><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #98C379\">&#39;John&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;Doe&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;Sales&#39;<\/span><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #98C379\">&#39;Emily&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;Brown&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;HR&#39;<\/span><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #98C379\">&#39;John&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;Doe&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;IT&#39;<\/span><span style=\"color: #ABB2BF\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Count duplicates based on first_name and last_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> first_name, last_name, <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> count<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> employees<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> first_name, last_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">HAVING<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Get the total number of duplicate rows<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> total_duplicates<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> first_name, last_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> employees<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> first_name, last_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">HAVING<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> subquery;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Bonus: Get all duplicate records<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> e.*<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> employees e<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">INNER JOIN<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> first_name, last_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> employees<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> first_name, last_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">HAVING<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">) dup <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">first_name<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">dup<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">first_name<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">last_name<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">dup<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">last_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">first_name<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">last_name<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s go through this example step by step:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>First, we create a sample <code>employees<\/code> table with some basic information.<\/li>\n\n\n\n<li>We insert sample data, including some deliberate duplicates (three &#8220;John Doe&#8221; entries).<\/li>\n\n\n\n<li>To count duplicates based on first_name and last_name:<\/li>\n<\/ol>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"   SELECT first_name, last_name, COUNT(*) as count\n   FROM employees\n   GROUP BY first_name, last_name\n   HAVING COUNT(*) &gt; 1;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #ABB2BF\">   <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> first_name, last_name, <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> count<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">   <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> employees<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">   <\/span><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> first_name, last_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">   <\/span><span style=\"color: #C678DD\">HAVING<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query will return:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"   first_name | last_name | count\n   -----------+-----------+------\n   John       | Doe       | 3\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #ABB2BF\">   first_name | last_name | count<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">   <\/span><span style=\"color: #7F848E; font-style: italic\">-----------+-----------+------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">   John       | Doe       | <\/span><span style=\"color: #D19A66\">3<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<ol class=\"wp-block-list\" start=\"4\">\n<li>To get the total number of duplicate rows:<\/li>\n<\/ol>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"   SELECT COUNT(*) as total_duplicates\n   FROM (\n       SELECT first_name, last_name\n       FROM employees\n       GROUP BY first_name, last_name\n       HAVING COUNT(*) &gt; 1\n   ) as subquery;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #ABB2BF\">   <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> total_duplicates<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">   <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">       <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> first_name, last_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">       <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> employees<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">       <\/span><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> first_name, last_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">       <\/span><span style=\"color: #C678DD\">HAVING<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">   ) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> subquery;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query will return:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"   total_duplicates\n   -----------------\n   3\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #ABB2BF\">   total_duplicates<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">   <\/span><span style=\"color: #7F848E; font-style: italic\">-----------------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">   <\/span><span style=\"color: #D19A66\">3<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<ol class=\"wp-block-list\" start=\"5\">\n<li>The bonus query shows how to retrieve all the duplicate records:<\/li>\n<\/ol>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"   SELECT e.*\n   FROM employees e\n   INNER JOIN (\n       SELECT first_name, last_name\n       FROM employees\n       GROUP BY first_name, last_name\n       HAVING COUNT(*) &gt; 1\n   ) dup ON e.first_name = dup.first_name AND e.last_name = dup.last_name\n   ORDER BY e.first_name, e.last_name;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #ABB2BF\">   <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> e.*<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">   <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> employees e<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">   <\/span><span style=\"color: #C678DD\">INNER JOIN<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">       <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> first_name, last_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">       <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> employees<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">       <\/span><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> first_name, last_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">       <\/span><span style=\"color: #C678DD\">HAVING<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">   ) dup <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">first_name<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">dup<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">first_name<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">last_name<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">dup<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">last_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">   <\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">first_name<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">last_name<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This will return all three &#8220;John Doe&#8221; records with their full details.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This example demonstrates how to identify duplicates, count them, and even retrieve the full records of duplicates if needed. The key is using <code>GROUP BY<\/code> to identify groups of records and <code>HAVING COUNT(*) &gt; 1<\/code> to filter for those groups with more than one record.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Practical Use Cases for Counting Duplicates<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>E-commerce Platforms<\/strong>: Detect duplicate customer accounts based on email addresses or phone numbers.<\/li>\n\n\n\n<li><strong>Sales Databases<\/strong>: Identify duplicate orders that may have been accidentally placed multiple times.<\/li>\n\n\n\n<li><strong>CRM Systems<\/strong>: Spot duplicate entries in contact lists to prevent redundancy in communications.<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Frequently Asked Questions (FAQ)<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Q: Can I count duplicates in multiple columns at once?<\/strong><br>A: Yes, you can count duplicates across multiple columns by grouping by those columns in your query.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Q: What\u2019s the difference between <code>HAVING COUNT(*) &gt; 1<\/code> and using a <code>DISTINCT<\/code> count?<\/strong><br>A: <code>HAVING COUNT(*) &gt; 1<\/code> identifies duplicate records, while <code>DISTINCT<\/code> counts the number of unique duplicates.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Q: Can I count duplicates without displaying them?<\/strong><br>A: Yes, you can modify the query to only display the count of duplicates without listing each duplicate record.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Q: How do I handle large datasets with many duplicates?<\/strong><br>A: For large datasets, consider indexing the columns you&#8217;re checking for duplicates to improve query performance.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Q: Can I use this method to count duplicates in related tables?<\/strong><br>A: Yes, you can join related tables and then apply the same counting logic to identify duplicates across those tables.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Summary: Count Duplicates in MySQL<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Counting duplicates in MySQL is a valuable skill that helps maintain data integrity and improve database performance. By using the methods outlined in this article, you can easily identify and manage duplicate records in your MySQL database.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When working with databases, it&#8217;s common to encounter situations where you need to identify and count duplicate records. In MySQL, detecting duplicates can help in data cleansing, auditing, and ensuring data integrity. Whether you\u2019re managing customer records, sales transactions, or any other dataset, counting duplicates can provide valuable insights. In this article, we&#8217;ll explore how [&hellip;]<\/p>\n","protected":false},"author":11,"featured_media":3566,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_eb_attr":"","_uag_custom_page_level_css":"","footnotes":""},"categories":[21],"tags":[],"class_list":["post-3564","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog"],"acf":[],"uagb_featured_image_src":{"full":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/08\/Five.Co-Count-Duplicates-in-MySQL-How-to-Guide.png",1280,720,false],"thumbnail":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/08\/Five.Co-Count-Duplicates-in-MySQL-How-to-Guide-150x150.png",150,150,true],"medium":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/08\/Five.Co-Count-Duplicates-in-MySQL-How-to-Guide-300x169.png",300,169,true],"medium_large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/08\/Five.Co-Count-Duplicates-in-MySQL-How-to-Guide-768x432.png",768,432,true],"large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/08\/Five.Co-Count-Duplicates-in-MySQL-How-to-Guide-1024x576.png",1024,576,true],"1536x1536":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/08\/Five.Co-Count-Duplicates-in-MySQL-How-to-Guide.png",1280,720,false],"2048x2048":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/08\/Five.Co-Count-Duplicates-in-MySQL-How-to-Guide.png",1280,720,false]},"uagb_author_info":{"display_name":"Ryan Forrester","author_link":"https:\/\/preview-php85.dzine.org\/five\/author\/f1v5_ed9_au\/"},"uagb_comment_info":0,"uagb_excerpt":"When working with databases, it&#8217;s common to encounter situations where you need to identify and count duplicate records. In MySQL, detecting duplicates can help in data cleansing, auditing, and ensuring data integrity. Whether you\u2019re managing customer records, sales transactions, or any other dataset, counting duplicates can provide valuable insights. In this article, we&#8217;ll explore how&hellip;","_links":{"self":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/3564","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/comments?post=3564"}],"version-history":[{"count":0,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/3564\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media\/3566"}],"wp:attachment":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media?parent=3564"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/categories?post=3564"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/tags?post=3564"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}