字符串聚合组并计数一个值

I have table like this.

我有这样的桌子。

 | table                        |
 | class_id| name    | gender   |
 +---------+---------+----------+ 
 |       1 | Jane    |     F    |
 |       1 | John    |     M    |
 |       1 | Tom     |     M    |
 |       1 | Bob     |     M    |
 |       2 | Jack    |     M    |
 |       2 | Kate    |     F    |

I have a query like this.

我有一个这样的查询。

select id, array_to_string(array_agg(name), ' - '::text) as name_list from table
group by class_id

My result is

我的结果是

 | 1 | Jane-John-Tom-Bob |

But i'd like to count my gender count also i mean in the first group (cass 1) i need a column like 1 F + 3 M

但是我想计算一下我的性别数我的意思是第一组(cass 1)我需要一个像1 F + 3 M的列。

My request is something like this and i'd like to use it in 1 group by.

我的请求是这样的，我想在1组by中使用它。

 | 1 | Jane-John-Tom-Bob |1F + 3M

2 个解决方案

#1

You can do that with a filtered aggregate:

你可以使用过滤后的聚合体:

select id, 
       string_agg(name), ' - ') as name_list, 
       concat( 
            count(*) filter (where gender = 'F'), 
            'F + ', 
            count(*) filter (where gender = 'M'), 
            'M') as gender_count
from table
group by class_id;

If you are on an older Postgres version, you need to replace

如果您使用的是较老的Postgres版本，则需要替换

count(*) filter (where gender = 'F')

with

与

count(case when gender = 'F' then 1 end)

(and the same for 'M')

(对M来说也是一样)

#2

There is also another solution without using Filter aggregate

还有一种不使用过滤器聚合的解决方案

select tt.class_id, string_agg ( t,  ','::text) as gender, string_agg(distinct y,','::text) as name

from 

(

select class_id, count(gender)::text|| string_agg( distinct  gender, ','   ) as  t
       from string_test
group by class_id , gender

) tt , 

(
select class_id, string_agg( distinct name::text, ','::text ) as y
    from string_test
group by class_id
) yy
where tt.class_id=yy.class_id

group by tt.class_id

Result;

结果;

+==========+========+===================+
| class_id | gender | name              |
+==========+========+===================+
| 1        | 1F,3M  | Bob,Jane,John,Tom |
+----------+--------+-------------------+
| 2        | 1F,1M  | Jack,Kate         |
+==========+========+===================+

#1