时间戳

首页 > 解决方案 > 如何将字段的总和聚合到字符串_聚合值 Postgres

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

问题描述

下面的查询向我显示了一个学生列表以及他们的基本信息。

我想汇总男孩和女孩的总和，而不必为他们显示单独的列。因此，我创建了 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
)

第2部分

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

标签： sqlpostgresqlaggregate

解决方案

count您可以通过Nationin selectthen usestring_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

推荐阅读