I did a query (pretty simple, with 4 columns x 100 000 rows) and there are redundancies (rows appear several times). I'd like to add a column that counts the number of times each row appears, and then remove the duplicates. So I don't lose information by adding this "count" column, and in the mean time lighten the table by deleting duplicate rows. For instance if I have this table:
| name | country | price |
|---|---|---|
| table | France | 12 |
| desk | Italy | 8 |
| table | France | 12 |
| desk | Italy | 8 |
| desk | Italy | 14 |
| desk | Italy | 8 |
And the output should be like:
| name | country | price | count |
|---|---|---|---|
| table | France | 12 | 2 |
| desk | Italy | 8 | 3 |
| desk | Italy | 14 | 1 |
CodePudding user response：
To count how many occurrences of a record that appears in a table, you can use the GROUP BY command, this will count how many occurrences depending on what you want to group by.
For instance, if you GROUP BY country then it will count how many records were found for each country.
Example: SELECT name, country, price, COUNT(*) FROM tablename GROUP BY name;
You can read up on GROUP BY here
CodePudding user response：
This query should work for you:
select `name`, `country`, `price`, count(*) from MyTable
group by `name`, `country`, `price`