SQL で家計簿のような科目別で集計した表を作成するテクニックを見て驚いたのでブログに残しておく。
環境を用意しないで SQL を試せる場所として SQL Fiddleが良かったので、使ってみてほしい。
画面構成
SQL Fiddleについて説明しておく。
アクセスすると次のような画面になる。
真ん中に左右大きく枠があり、下部に横長の枠がある。
まずは左右の左側だが、こちらにはテーブルの情報やデータの挿入などを実施する部分になる。
次に左右の右側で、こちらはテーブルから情報を取得する SELECT 文を書く場所になる。
最後に下の横長になるが、こちらは実行結果が表示される部分になる。
細かい部分としては左上のSQL Fiddleの右側でデータベースエンジンを切り替えることができる。
今回は SQLite(WebSQL) を使ったので、この先の SQL を実施する場合は切り替えてほしい。
サンプル構成
テーブルは次のものになる。
テーブル
サンプル説明
テーブルを作成しデータを投入する 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
- 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
Communication expenses
で計算結果もあっている。
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 |
今の考え方を使えば、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 はもちろん知っていたが、なかなか使う機会がなかったので具体的な方法が知れてよかった。