よしたく blog

ITエンジニアとして自分が知らなかったことをまとめています

SQLで勘定科目内訳書を作成する

SQL で家計簿のような科目別で集計した表を作成するテクニックを見て驚いたのでブログに残しておく。

環境を用意しないで SQL を試せる場所として SQL Fiddleが良かったので、使ってみてほしい。

画面構成

SQL Fiddleについて説明しておく。 アクセスすると次のような画面になる。 真ん中に左右大きく枠があり、下部に横長の枠がある。

f:id:yoshitaku_jp:20210516100150p:plain

まずは左右の左側だが、こちらにはテーブルの情報やデータの挿入などを実施する部分になる。 次に左右の右側で、こちらはテーブルから情報を取得する SELECT 文を書く場所になる。 最後に下の横長になるが、こちらは実行結果が表示される部分になる。

細かい部分としては左上のSQL Fiddleの右側でデータベースエンジンを切り替えることができる。 今回は SQLite(WebSQL) を使ったので、この先の SQL を実施する場合は切り替えてほしい。

サンプル構成

テーブルは次のものになる。

テーブル

  • id
  • type
    • 家計簿の科目
  • stuff
    • 具体的なもの
  • money
    • 金額

サンプル説明

DDL

テーブルを作成しデータを投入する SQL 文は次のものになる。

-- this version is using your browser's built-in SQLite
CREATE TABLE HouseholdAccountBook
    (
     id integer primary key,
     type varchar(20),
      stuff varchar(20),
     money varchar(30)
    );

INSERT INTO HouseholdAccountBook
(id, type,stuff, money)
VALUES
(1, 'Food expenses','lunch', '1000');

INSERT INTO HouseholdAccountBook
(id, type,stuff, money)
VALUES
(2, 'Food expenses','dinner', '1500');

INSERT INTO HouseholdAccountBook
(id, type,stuff, money)
VALUES
(3, 'Communication expenses','Mobile phone bill','5000');

INSERT INTO HouseholdAccountBook
(id, type,stuff, money)
VALUES
(4, 'Communication expenses','Internet fee','6000');

投入した具体的なデータ

SQL 文の中に記述されている投入したデータもこちらにまとめておく。

  • Food expenses
    • lunch(ランチ)
    • dinner(夜ご飯)
  • Communication expenses
    • Mobile phone bill(携帯代)
    • Internet fee(インターネット料金)

まずはそのまま取得する

select
  id
  ,type
  ,stuff
  ,money
  from HouseholdAccountBook
id type stuff money
1 Food expenses lunch 1000
2 Food expenses dinner 1500
3 Communication expenses Mobile phone bill 5000
4 Communication expenses Internet fee 6000

各科目の合計が知りたい

ここで Food expenses ごと、Communication expenses ごとの合計が知りたいとする。 次のような SQL になる。

select
  type
  ,SUM(money)
from HouseholdAccountBook
GROUP BY type
type SUM(money)
Communication expenses 11000
Food expenses 2500

2 つの結果をくっつけたい

この結果をくっつけて Food expenses の合計と Communication expenses の合計も一緒に表示したいときは UNION ALL を使う。 各科目の合計を取得した SQL を少し変えて、次のようにする。

select
  id
  ,type
  ,stuff
  ,money
from HouseholdAccountBook

UNION ALL

select
  MAX(id)
  ,type || ' - total'
  ,'' as stuff
  ,SUM(money)
from HouseholdAccountBook
  GROUP BY type

ORDER BY id

合計の行を MAX(id)で取得し、order by することで科目毎の最下部に位置できる。

  • Food expenses
    • 1000 + 1500 = 2500
  • Communication expenses

    • 5000 + 5000 = 11000

    で計算結果もあっている。

id type stuff money
1 Food expenses lunch 1000
2 Food expenses dinner 1500
2 Food expenses - total 2500
3 Communication expenses Mobile phone bill 5000
4 Communication expenses Internet fee 6000
4 Communication expenses - total 11000

最終的な SQL

今の考え方を使えば、Food expenses と Communication expenses を合計したものも取得できる。 SQL は長くなるのでまずは結果から載せる。最終行に全ての合計が存在している。

id type stuff money
1 Food expenses lunch 1000
2 Food expenses dinner 1500
2 Food expenses - total 2500
3 Communication expenses Mobile phone bill 5000
4 Communication expenses Internet fee 6000
4 Communication expenses - total 11000
4 total 13500
select
id
,type
,stuff
,money
from HouseholdAccountBook

UNION ALL

select
MAX(id)
,type || ' - total'
,'' as stuff
,SUM(money)
from HouseholdAccountBook
GROUP BY type

UNION ALL

select
MAX(id)
,'total'
,'' as stuff
,SUM(money)
from HouseholdAccountBook

ORDER BY id

まとめ

SQL で勘定科目内訳書のようなものを作成する Tips を投稿した。 テーブルを縦に結合する UNION ALL はもちろん知っていたが、なかなか使う機会がなかったので具体的な方法が知れてよかった。