sql - 如何将字段的总和聚合为字符串_聚合值Postgres

Question

下面的查询显示了一个学生列表及其基本信息。

我想汇总男孩和女孩的总和，而不必为他们显示单独的列。所以我创建了 2 列，然后我将它们的总和显示在最后一行。

第 1 部分

with ext as (
select s.studentid as SID,p.surname AS Lastname,
        p.firstname AS Firstname,
        p.sex AS Gender,
        p.birthdate AS BDate,
        ctf.name as Nation,
      SUM(CASE WHEN p.sex = 'MALE' THEN 1 ELSE 0 END) AS BoyCount,
      SUM(CASE WHEN p.sex = 'FEMALE' THEN 1 ELSE 0 END) AS GirlCount
from students s
        join pupil p on p.id = s.pupilid
        join pupilnation pn on pn.pupilid = p.id
        join country ctf on ctf.id = pn.coutnryid
        ...   
group by s.studentid, p.surname, p.firstname,p.sex,p.birthdate,ctf.name
)

第二部分

select SID,Lastname,Firstname,Gender,BDate,Nation
from ext
union all
select 'Students: ' || cast(count(SID) as varchar(6)), 
    null as Lastname, 
    null as Firstname,
    'Boys: ' || cast(sum(boycount) as varchar(6)) || '   Girls: ' || cast(sum(girlcount) as varchar(6)),
    null as Bdate,
    string_agg(distinct Nation,',')

结果

SID     Firstname   Gender  Bdate       Nation
723785  Saria       FEMALE  20.01.2012  France
45949   Wenzel      MALE    08.11.2011  Germany
3373    Constantin  MALE    19.03.2006  Germany
727578  Laurin      MALE    08.04.2012  Germany
157     Valerian    MALE    15.01.2008  UK
595959  Attila      MALE    08.06.2012  USA
4172    Sophie      FEMALE  01.11.2004  France
693465  Ibrahim     MALE    16.05.2011  Belgium
…                   
…                   
12 Students         8 Males 4 Females   Germany, France, UK, US, Ughanda

预期结果

我想在字符串聚合中将每个国家的总和附加到其字符串。这是否可能如下所示？例如。德国:5，法国:3，英国:2，美国:1，乌干达:1

SID     Firstname   Gender  Bdate       Nation
723785  Saria       FEMALE  20.01.2012  France
45949   Wenzel      MALE    08.11.2011  Germany
…                   
…                       
12 Students         8 Males 4 Females   Germany: 5, France: 3, UK: 2, US: 1, Ughanda: 1

Answer 1

您可以在 select 中按 Nation 编写子查询 count 然后使用 string_agg 函数。

select SID,Lastname,Firstname,Gender,BDate,Nation
from ext
union all
select 'Students: ' || cast(count(SID) as varchar(6)), 
    null as Lastname, 
    null as Firstname,
    'Boys: ' || cast(sum(boycount) as varchar(6)) || '   Girls: ' || cast(sum(girlcount) as varchar(6)),
    null as Bdate,
    (
        select  string_agg(cnt , ', ') from 
        (
          SELECT Nation||':'||COUNT(*) cnt
          FROM ext
          GROUP BY Nation
        ) t1
    )
FROM ext

或者你可以使用CROSS JOIN来实现

SELECT 
    'Students: ' || cast(totalCnt as varchar(6)), 
    null as Lastname, 
    null as Firstname,
    'Boys: ' || cast(boyCnt as varchar(6)) || '   Girls: ' || cast(girlCnt as varchar(6)),
    null as Bdate,
    v.Nation
FROM (
    select  
       sum(boycount) boyCnt,
       sum(girlcount) girlCnt,
       count(SID) totalCnt
    FROM ext
) t1 
CROSS JOIN (
  select string_agg(cnt , ', ') Nation from 
  (
    SELECT Nation||':'||COUNT(*) cnt
    FROM ext
    GROUP BY Nation
  ) t1
) v