【Postgresql】総合計を計算する
テーブルの構造とデータの状態です
select * from bs_summary ;
agg_code | debit | credit
----------+---------+--------
1002 | 1000000 |
1001 | 1000000 |
2001 | | 800000
2002 | | 800000
(4 rows)
各列の合計を最後に出力する
select coalesce(agg_code,'9999') as agg_code,
sum(debit) as debit,
sum(credit) as credit
from bs_summary
group by rollup(agg_code)
order by agg_code;
agg_code | debit | credit
----------+---------+---------
1001 | 1000000 |
1002 | 1000000 |
2001 | | 800000
2002 | | 800000
9999 | 2000000 | 1600000
(5 rows)