The mode is the value that appears most often in a set.
I would like something like:
SELECT
t.id as t_id,
GROUP_CONCAT(t.value) as value_list,
MODE(t.value) AS value_mode
FROM dataset.table as t
GROUP BY t_id
such that, for example:
t_id value_list value_mode
1 2,2,2,3,6,6 2
How is that done?
EDIT: The value_list is just there for illustration purpose. Only need the mode
select id, value as value_list, v as value_mode
from (
select
id, value, v,
count(1) as c,
row_number() over(partition by id order by c desc) as top
from (
select id, value, split(value) as v
from dataset.table
)
group by id, value, v
)
where top = 1