我有一個復雜的查詢，我在不同的列中獲取各種類別的計數。
這是我的查詢的輸出：
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 by rollup與上述查詢一起使用：
...
group by rollup (q1."district")
它在底部添加一行，但值類似于它之前一行的值，而不是它之前所有行的總和，所以基本上是這樣的：
district | colA | colB | colC
------------------------------------
DistA | 1 | 1 | 3
..
DistD | 0 | 3 | 4
Total | 0 | 3 | 4
那么，我怎樣才能從我的查詢中得到一些列呢？
uj5u.com熱心網友回復：
試試這個：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/480004.html
標籤：sql PostgreSQL 通过...分组 卷起