MYSQL Remove Duplicates and add Column of their Cnt

Question

I have a mysql table with 6 columns and thousands of rows. I want to add another column titled "count of reports". To begin, it should be known that some rows in this table are identical. They are not exactly duplicates, but rather there is a separate instance of the same exact values. So what I would like to do is collapse all of the duplicate rows into one row and add the count of these (so count of all of the duplicates plus 'original) to the adjacent "count of reports" column. Any row in which there is only that row would therefore have an adjacent "count of reports" value of 1. How would I go about this. For the purpose of this query, my table name is company, database is companies, and the six initial column names are seller, one, two, three, four, and types.

The following query gives the entire table, with six columns and about two thousand rows.

select seller, IFNULL(one,""), IFNULL(two,""), IFNULL(three,""), 
IFNULL(four,""), types from companies.company  
order by seller, one, two, three, four, types;

What would I add to this/how would I change it to count the duplicate rows of the table, remove them, and add the count to an adjacent column?

Gordon Linoff Gordon Linoff · Accepted Answer · 2015-01-11T16:26:46

I think you just want group by:

select  seller, one, two, three, four, types, count(*)
from companies.company c
group by seller, one, two, three, four, types, count(*);

If you actually want to modify the table, then I would recommend the following:

select the above results into a temporary table
truncate the original table
add a count column
insert the data back into the table

However, I'm not convinced that you really want to change the source data.

MYSQL Remove Duplicates and add Column of their Cnt

1 Answers