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



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?

enter image description here

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:


the user 42890 have three times in the result.

  • Split CSV to separate values, then concat distinct values back.
    – Akina
    Mar 16, 2021 at 9:24

1 Answer 1


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.