SQL query get all duplicate records with count
<p>I have a table with duplicate data in a certain column (<code>state</code>). I need to fetch all rows and also display the <code>COUNT()</code> of duplicate records.</p>
<h3>Sample Data</h3>
<pre><code>name     state  email
----     ----   -----
john     ny     john@ny.com
smith    ny     smith@ny.com
bryan    nj     bryan@nj.com
</code></pre>
<p>I need to write a SQL query to display records like this, that includes the number of duplicate <code>state</code> values on each row:</p>
<pre><code>name     state  email         count
----     ----   -----         -------
john     ny     john@ny.com      2
smith    ny     smith@ny.com     2
bryan    nj     bryan@nj.com     1
</code></pre>