画面構成

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