【試して覚える】SQLite で Select 文入門（Groupと集計関数）

これまでに、select文 でデータを抽出する方法について解説してきましたが、実際には抽出した結果を合計したり平均することの方が多いかと思います。

今回は、指定した条件に一致するデータについて平均や合計などの処理を行ってみたいと思います。

SQLを試すためのツールは、あらかじめ こちら からダウンロードしておいて下さい。

Group Byと集計関数

グループ化（グルーピング）というのは、指定したカラムの値が同じものを１つのグループとして扱おうというものです。

例えば、下記の様なデータに対して「メーカー」というカラムでグルーピングした場合、"CANON"と"SONY"の２つのグループが出来上がります。

グルーピングは、複数のカラムを使うことも可能です。

次の例は売上日とメーカーでグルーピングした例ですが、売上日ごと、メーカーごとにグループが出来上がっています。

Group Byでグルーピング

グルーピングさせるには、Group By の後に、続けてグルーピングしたいカラム名を列挙します。

Group by カラム名１、カラム名２、・・・

Gruop by は select 文の最後に記述して下さい。

例えば次のようになります。

select * from 商品マスタ group by メーカー

では、さっそくいつものツールを使って実験してみましょう。

group by を付けた場合と付けない場合の違いを確認するため、初めに、商品マスタを全件表示してみます。

次のSQLを実行してみて下さい。

select * from 商品マスタ

もしくは、ツールの「テーブル一覧」に表示されている「商品マスタ」をダブルクリックしていただいても結構です。

93件表示されていますね。

では次に、Group Byを付けて実行してみて下さい。

select * from 商品マスタ group by メーカー

結果は以下の通りになります。

11件がヒットしましたね。

Gourp By を付けた場合と付けない場合の違いを確認いただけたかと思います。
Group By を付けた方が件数が減ってますよね。
グルーピングは出来ましたが、単にデータが欠落したかの様な状態になりました。

ここで、「メーカー」のカラムに着目して下さい。

全てのメーカー名が１件ずつ表示されていることが分かると思います。

「メーカー」でクルーピングしたのですが、特に集計方法を指定していなかったので、それぞれ最初に見つかった1件だけが代表で表示されています。

グルーピングしたデータを集計する

グルーピングをする目的は集計なので、Group by を使う場合は必ず集計関数を用います。

集計関数は、関数名（集計対象カラム）という記述をします。
例えば、合計を求める集計関数は sum なので、「単価」というカラムを合計したい場合は次の様になります。

suｍ(単価)

これを select に続けて記述するのですが、この時に注意して頂きたい事が1点あります。

必ず group by で指定したカラムを select の直後に付加するという点です。
例えば、「メーカー」でグルーピングしているなら、次の様になります。

select カラム名1,カラム名2 , sum(カラム名3) from 商品マスタ group by カラム名1,カラム名2

では、さっそく試してみましょう。

select メーカー,sum(単価) from 商品マスタ group by メーカー

結果は次の様に、メーカーごとに単価が合計されて表示されます。

今度は、「発売日」と「メーカ」でグループ化して、単価を合計してみましょう。

group by に「発売日」と「メーカー」を指定するので、 select の後にも「発売日」と「メーカー」を記述しています。

select 発売日,メーカー,sum(単価) as 合計　 from 商品マスタ group by 発売日,メーカー

ちなみに、 sum(単価) as 合計 　と言う記述は、sum(単価) を 「合計」という名前で表示せよという指示で、この「合計」のことを「エイリアス名」という呼び方をします。

では、お試しください。

次のような結果になれば正解です。

では、もう少し複雑なものに挑戦してみましょう。

売上データを商品マスタを使って、「売上日」ごと、「メーカー」ごとの数量と売上金額（数量×単価）を、「売上日」「メーカ」順にソートして表示してみます。

まず、売上データに商品マスタを結合(Join)してみます。

寮テーブルとも、「商品コード」でJoin できますので、次の様になります。

select * from 売上データ t1
left join 商品マスタ t2 on t1.商品コード=t2.商品コード

結果は次のようになりました。

「売上日」は売上データ、「メーカー」は商品マスタという別々のテーブルにあるカラムですが、Joinすることで、同時にグルーピングできるようになります。

売上金額は単価×金額ですから、sum(t1.数量) * t2.単価 という記述になります。

select
t1.売上日
,t2.メーカー
,sum(t1.数量) as 数量
,sum(t1.数量) * t2.単価 as 売上金額
from 売上データ t1
left join 商品マスタ t2 on t1.商品コード=t2.商品コード
group by t1.売上日,t2.メーカー
order by 売上日,メーカー

少し複雑になりましたが、結果は次の様になります。

集計関数一覧

SQLiteで用意されている集計関数には、次のようなものが有ります。

ちなみに、これらは他のデータベースでも用意されています。

関数名	動作
count	レコード数を返します。count(*) は無条件にレコード数を求めるのに対し、count(カラム名）の場合は、そのカラムの値がNULLでないレコード数を返します。
sum	カラムの合計を返します。カラムの値が整数値ばかりなら整数値を、浮動小数点が含まれていると浮動小数点として合計が返されます。
total	カラムの合計を浮動小数点で返します。カラムの値が整数ばかりであっても浮動小数点を返します。また、カラムの値がNULLばかりだった場合、0.0を返します。
avg	カラムの平均を返します。
max	カラムの最大値を返します。
min	カラムの最小値を返します。

集計関数を使う注意点としては、カラムの型がINTEGERやREALでない場合、たとえ数値が入っていても正しい結果が返りません。

エラーではなく何らかの数値が返ってくるので気づきにくいのですが、もし正しい値が返ってこないのであれば、CASTを使ってINTEGERやREALに型変換することで解決できます。

CAST（カラム名 as データ型）

例えば、「金額」というカラムを浮動小数点に変換する場合 realに変換する場合、次の様に記述します。

cast( 金額 as real）

集計結果を検索条件に使う（having）

集計関数の値を抽出条件に入れたい場合、例えば max(金額) が 10000 以上のものだけ表示したい場合を考えてみましょう。

例えば、売上日毎の数量が10以上のデータだけを抜き出したい場合です。

単純に where sum(数量) >= 10 と記述したいところですが、where 区は group by より先に記述する必要があるので、group by の後に where 区を書くとエラーになります。

そこで、 having が登場します。

これは 集計関数の結果で絞り込み条件を行いたい時に用いるもので、group by の後に記述します。

先ほどのSQLに対して、sum(t1.数量) >=10 で抽出条件を指定したい場合、次の様になります。

select
t1.売上日
,t2.メーカー
,sum(t1.数量) as 数量
,sum(t1.数量) * t2.単価 as 売上金額
from 売上データ t1
left join 商品マスタ t2 on t1.商品コード=t2.商品コード
group by t1.売上日,t2.メーカー
having sum(t1.数量) >= 2

実行結果は次の様になります。

sum(t1.条件) でソートしたい場合は、最後に order by を指定します。

select
t1.売上日
,t2.メーカー
,sum(t1.数量) as 数量
,sum(t1.数量) * t2.単価 as 売上金額
from 売上データ t1
left join 商品マスタ t2 on t1.商品コード=t2.商品コード
group by t1.売上日,t2.メーカー
having sum(t1.数量) >= 10 order by sum(t1.数量)

集計結果を検索条件に使う（入れ子）

having を使うという方法以外にも、集計結果で絞り込むことが可能です。

SQLは入れ子に出来るので、全体を select 文で囲ってあげて、そこで条件を指定するという方法も可能です。

select * from (
select
t1.売上日
,t2.メーカー
,sum(t1.数量) as 数量
,sum(t1.数量) * t2.単価 as 売上金額
from 売上データ t1
　 left join 商品マスタ t2 on t1.商品コード=t2.商品コード
group by t1.売上日,t2.メーカー
) where 数量 >= 10

まとめ

今回は グルーピングと集計関数の使い方を解説しました。

Group By で指定したカラムは、必ず　select 文の最初に記述するという点はポイントとして押さえておいて下さい。

あと、SQLite のデータ型は要注意で、テーブルを作る際に明示的に REALやINTEGER を指定しないと、何でも入るカラムになってしまいます。

こうなると、集計関数で間違った値が返されますので、その点はご注意ください。