我正在尝试做一个聚合，它采用组中最常见的值，如下所示：

with t1 as (
    select
        id
        , colA
        , colB
    from some_Table
)
select 
    id
    , count(*) as total
    , max(colA) as maxColA
    , most_common(colB) -- this is what I'm trying to achieve
from t1
group by id

这就是我所尝试的：

with t1 as (
    select
        id
        , colA
        , colB
    from some_Table
)
select 
    id
    , count(*) as total
    , max(colA) as maxColA
    , (select colB, count(colB) as counts from t1 group by colB order by counts desc limit 1) as most_freq_colB_per_id
from t1
group by id

然而，它告诉我 AnalysisException: Subqueries are not supported in the select list . 我还能怎么做？