Mysql – how to using group concat distinct count in MySQL 5.7

MySQL

Now I am using MySQL 5.7, I have some data like this:

user_id

63763
63763
NULL
16459,16535,16613,16615,42896,42911,62463,62464,63594,63763,63764,63765,63766,63769,63930,63934,63935
NULL
NULL

I want to concat the user_id and distinct , finnally count the result of distinct result(finally I find the result still have duplicate value), is it possible to complete it in one sql? I known distinct like this:

select group_concat(DISTINCT user_id SEPARATOR ',')
from report_summary
where statistic_time >= 1615824000000

how to count the result?

and now I found the result still have duplicate value using this sql:

select group_concat(DISTINCT bet_user_ids SEPARATOR ',')
from report_summary
where statistic_time >= 1616083200000
and statistic_time <= 1616169599999

the result is:

16459,16535,16613,16615,18627,42890,42896,46521,62463,62464,63591,63595,63763,63764,63765,63769,63934,63939,63940,42890,42896,42890,42896,62463,63763

the user 42890 have three times in the result.

Best Answer

There's no direct way to do this with one operator, but you can use the following trick to accomplish it:

select group_concat(DISTINCT user_id SEPARATOR ','),
    LENGTH(group_concat(DISTINCT user_id SEPARATOR ',')) - LENGTH(REPLACE(group_concat(DISTINCT user_id SEPARATOR ','), ',', '')) + 1 AS IdCount
from report_summary
where statistic_time >= 1615824000000

Basically it counts the number of commas (+ 1) in the list by subtracting the length of the string after removing the commas from the original string's length. I thought it was pretty nifty and found it in this StackOverflow answer.

Related Solutions

MySQL – GROUP_CONCAT returns duplicate data, can’t use DISTINCT

You have identified the source of the problem: that recipe is joined to two tables, recipe_detail and recipe_tagmap (and these to several other tables related to respectively "ingredients" and "tags"), and recipe is having one-to-many relationships with both of them.

One solution is to individually GROUP BY and aggregate first (one aggregation for the list of the tables related to ingredients and another for the group of tables related to tags, and then join back (again) to the main table (recipe):

SELECT recipe.*, 
       iid,  
       iname, 
       mabbr, 
       tag
FROM  recipe

  LEFT JOIN 
    ( SELECT recipe_detail.recipe_id,
             GROUP_CONCAT(recipe_detail.ingredient_id) AS iid,  
             GROUP_CONCAT(ingredient.name) AS iname, 
             GROUP_CONCAT(ingredient_mfr.abbr) AS mabbr
      FROM recipe
        JOIN recipe_detail
          ON recipe.id = recipe_detail.recipe_id
        LEFT JOIN ingredient
          ON recipe_detail.ingredient_id = ingredient.id
        LEFT JOIN ingredient_mfr
          ON ingredient.mfr_id = ingredient_mfr.id
      WHERE recipe.user_id = 1
      GROUP BY recipe_detail.recipe_id
    ) AS details
        ON recipe.id = details.recipe_id

  LEFT JOIN
    ( SELECT recipe_tagmap.recipe_id,
             GROUP_CONCAT(recipe_tag.name) AS tag 
      FROM recipe
        JOIN recipe_tagmap
          ON recipe.id = recipe_tagmap.recipe_id
        LEFT JOIN recipe_tag
         ON recipe_tagmap.tag_id = recipe_tag.id
      WHERE recipe.user_id = 1
      GROUP BY recipe_tagmap.recipe_id
    ) AS tags
      ON recipe.id = tags.recipe_id

WHERE recipe.user_id = 1 ;

Tested at: SQL-Fiddle

(Using the recipe table inside the 2 aggregations is not strictly needed but since you only want the recipes of one user, it will help for efficiency, restricting the number of rows retrieved from several tables and aggregated.)

Mysql – Limit distinct count of group by

You can test this variation. In theory, it would use an (id, value) index to find the min and max and would not have to count the distinct values at all:

SELECT id
FROM t
GROUP BY id
HAVING MIN(value) < MAX(value) ;

Best Answer

Related Solutions

MySQL – GROUP_CONCAT returns duplicate data, can’t use DISTINCT

Mysql – Limit distinct count of group by

Related Question