postgresql - 如何通过其他列选择唯一字符串来计算列中的字符串

Question

试图解决问题发现，但这不是我需要的...

select distinct column1, count(column2) from table group by column1;


    column1      column2
    aaa          type1
    bbb          type3
    ccc          type1
    aaa          type1
    aaa          type2
    aaa          type1
    ccc          type3
    aaa          type1
    bbb          type3
    aaa          type3
    aaa          type3

预期结果是:

    aaa type1 = 4, type2 = 1, type3 = 2    bbb type3 = 2    ccc type1 = 1, type3 = 2

Answer 1

select 
    column1, 
    string_agg(format('%s = %s', column2, count), ', ') counts
from (
    select *, count(column2) 
    from test 
    group by 1, 2
    order by 1, 2
    ) s
group by 1
order by 1

 column1 |             counts              
---------+---------------------------------
 aaa     | type1 = 4, type2 = 1, type3 = 2
 bbb     | type3 = 2
 ccc     | type1 = 1, type3 = 1
(3 rows)