CODEWITHSUNDEEP
mysqlsql
Altimus Prime
Altimus Prime

Reputation: 2327

Multiple duplicated rows returned on GROUP BY query in mysql

I use the following mysql query thinking it would give me a table of unique rows grouped by fields given. I end up with a few rows where the grouped by fields are duplicated but the unique id and count are the only thing that's different.

There are tens of thousands of rows, but only a couple hundred that are repeated with no visible difference in the field. What might cause this?

 CREATE TABLE group_avg_product_prices AS
        SELECT group_id,
        feature_1, 
        feature_2, 
        feature_3,
        AVG(price) AS avg_price,
        count(*) AS sample_count, 
        GROUP_CONCAT(unique_id SEPARATOR ",") AS items
 FROM products_table
 GROUP BY group_id,
          feature_1, 
          feature_2, 
          feature_3

returns rows that are something like:

asdf123 | 2 | 1| .5| 2.65| 3| id1,id2,id3
asdf123 | 2 | 1| .5| 2.34| 2| id4,id5

It's driving me nuts and I can't find any reason why it might do this.

Thanks in advance for any help.

Upvotes: 0

Views: 79

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Based on your sample data, the fourth column is numeric. It might look like 0.5, but the values are probably different, such as 0.499999 and 0.500001.

My suggestion is to use format() -- either on the output so you can see the difference, or in the query itself. For instance, to combine two rows with similar values up to the first decimal place:

 SELECT group_id, feature_1,  feature_2, 
        format(feature_3, 1) as feature_3,
        AVG(price) AS avg_price,
        count(*) AS sample_count, 
        GROUP_CONCAT(unique_id SEPARATOR ',') AS items
 FROM products_table
 GROUP BY group_id, feature_1, feature_2, 
          format(feature_3, 1);

Upvotes: 1

Related Questions