如何在SQL中获取列明智的总和？

Question

我有一个复杂的查询，其中我可以在单独的列中获得各种类别的计数。

这是我查询的输出：

    district |  colA  |  colB  |  colC
    ------------------------------------
    DistA    |  1     |   1    |   3
    DistB    |  2     |   0    |   2
    DistC    |  2     |   1    |   0
    DistD    |  0     |   3    |   4
    ..

这是我的查询：

select
  q1."district",
  coalesce(max(case q1."type" when 'colA' then q1."type_count" else 0 end), 0) as "colA",
  coalesce(max(case q1."type" when 'colB' then q1."type_count" else 0 end), 0) as "colB",
  coalesce(max(case q1."type" when 'colC' then q1."type_count" else 0 end), 0) as "colC"
from (
  select
    d."name" as "district",
    t."name" as "type",
    count(t.id) as "type_count"
  from
    main_entity as m
  inner join type_entity as t on
    m."type_id" = t.id
  inner join district as d on 
    m."district_id" = d.id
  where
    m."delete_at" is null
  group by
    d."name",
    t.id
) as q1
group by
  q1."district"

我想修改此查询，以便我可以在最后一行中获取每列的总和，类似的内容：

 district |  colA  |  colB  |  colC
    ------------------------------------
    DistA    |  1     |   1    |   3
    DistB    |  2     |   0    |   2
    DistC    |  2     |   1    |   0
    DistD    |  0     |   3    |   4
    ..
    Total    |  5     |   5    |   9

我尝试使用>使用group。 + lollup带有上述查询，只需添加以下内容：

...
group by rollup (q1."district")

它在底部添加一个行，但值与前一行的值相似，而不是前行之前的所有行总和，基本上是这样的东西：

 district |  colA  |  colB  |  colC
    ------------------------------------
    DistA    |  1     |   1    |   3
    ..
    DistD    |  0     |   3    |   4
    Total    |  0     |   3    |   4

那么，我该如何从查询中获得一些列？

Answer 1

尝试以下操作：

With temp as 
(    --your query from above
    select
      q1."district",
      coalesce(max(case q1."type" when 'colA' then q1."type_count" else 0 end), 0) as "colA",
      coalesce(max(case q1."type" when 'colB' then q1."type_count" else 0 end), 0) as "colB",
      coalesce(max(case q1."type" when 'colC' then q1."type_count" else 0 end), 0) as "colC"
    from (
      select
        d."name" as "district",
        t."name" as "type",
        count(t.id) as "type_count"
      from
        main_entity as m
      inner join type_entity as t on
        m."type_id" = t.id
      inner join district as d on 
        m."district_id" = d.id
      where
        m."delete_at" is null
      group by
        d."name",
        t.id
    ) as q1
    group by
      q1."district"
)

select t.* from temp t
UNION
select sum(t1.colA),sum(t1.colB),sum(t1.colC) from temp t1