how to using group concat distinct count in MySQL 5.7

Question

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.

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

J.D. · Accepted Answer · 2021-03-16 11:38:16Z

2

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.

edited Mar 16, 2021 at 11:38

answered Mar 16, 2021 at 11:11

J.D.

41.2k12 gold badges64 silver badges147 bronze badges

Add a comment |

Stack Exchange Network

how to using group concat distinct count in MySQL 5.7

1 Answer 1

Your Answer

Hot Network Questions

how to using group concat distinct count in MySQL 5.7

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions