売上集計（ExecuteSQL）・その２

前回は、商品の一覧というテーブルに対して、商品の一覧のデータをくださいというSQLを唱えて、そのデータを取ってきました。

FileMakerのテーブルに対してSQLを唱えて、必要なデータを取ってくる。

ExecuteSQL関数の基本的な使い方をやりました。

次は売上データというテーブルに対してSQLを唱えます。

SQLの内容は、前回よりもちょっとだけ発展。

商品の売上データをください、ついでに商品ごとに売上個数と金額を合計してください。

という命令です。

この結果、苺のショートケーキは何個、チーズケーキは何個売れましたよ、というデータが取れます。

デモ用ファイル

２つのファイルを同じフォルダに置いて、main.fmp12の方を開いてください。

※FileMakerのバージョン12以上の環境が必要です。

集計ボタンを押すと、集計結果が出ます。

左から、商品ID、商品名、商品分類ID、商品分類名、売上個数、売上金額です。

SQLの到着先は、data.fmp12（外部データソース）の「売上明細」テーブルです。

data.fmp12を開いて「売上明細」レイアウトを見てもらうとわかりますが、売上１件を１レコードとして記録していったものです。

ExecuteSQL関数は、ほぼ同じです。

SQLの部分が少し違います。

SELECT \"商品ID\", \"商品名\", \"商品分類ID\", \"商品分類名\", SUM(\"数量\"), SUM(\"金額\")

  FROM \"売上明細\"

  GROUP BY \"商品ID\", \"商品名\", \"商品分類ID\", \"商品分類名\"

SELECTにはテーブルの項目名を、FROMにはテーブル名を書くことで、このテーブルからこの項目のデータを取ってきてください、ということでした。

ただし、今回はSUMという部分と、GROUP BYという部分が新要素です。

SUM(\"数量\")は、数量という項目のデータを選んで、合計してくださいという意味になります。

全商品の売上数量でいいなら、GROUP BYは不要です。

しかし、商品「ごと」に数量の合計を出したい場合は、GROUP BYが必須です。

売上明細を眺めると、ここには同じ商品の売上データが重複してますね。

同じ商品を買っていくお客さんがいるので、当然です。

GROUP BYは、このような重複を除いて、売上明細をグループ分けしてくださいという命令です。

----------------------------

苺のショートケーキ　１個

苺のショートケーキ　２個

チーズケーキ　３個

チーズケーキ　２個

クロワッサン　１個

----------------------------

というデータしかなければ、グループ分けと先ほどのSUMによって

----------------------------

苺のショートケーキ　３個

チーズケーキ　５個

クロワッサン　１個

----------------------------

という結果になります。

SUMはグループごとに計算してくれるわけです。

注意したいのは、SELECTの後に書いたSUM以外の項目名は、GROUP BYの後にも全部書かないとダメだということ。

上の呪文の場合、GROUP BYの後に商品IDと商品名しかないケースは、エラーになります。

なぜかというと、仮に以下のようなデータがあったとしますね。

--------------------------------

苺のショートケーキ　スイーツ　１個

苺のショートケーキ　スイーツ　４個

苺のショートケーキ　スウィーツ　２個

苺のショートケーキ　スウィーツ　１個

チーズケーキ　スイーツ　３個

チーズケーキ　スイーツ　２個

クロワッサン　パン　１個

--------------------------------

このデータ、商品だけでグループ分けして、合計数出してよ、と言われたら、

--------------------------------

苺のショートケーキ　８個

チーズケーキ　５個

クロワッサン　１個

--------------------------------

こうなりますよね。ここで、

「あれ、分類名は？分類名も表示してよ」

と言われたら、困ったことになりませんか。

苺のショートケーキ　８個

の行は、「スイーツ」と「スウィーツ」の分類がまとまっているので、どちらを表示していいかわからないのです。

ですので、分類名も表示したい場合、商品と商品分類でグループ分けをする必要があります。

--------------------------------

苺のショートケーキ　スイーツ　５個

苺のショートケーキ　スウィーツ　３個

チーズケーキ　スイーツ　５個

クロワッサン　パン　１個

--------------------------------

これが正解になります。

SELECTで選ぶ表示したい項目名と、GROUP BYで選ぶグループ化したい項目名は、よく考えましょう。

先ほどのスイーツ・スウィーツ問題のように、矛盾が生まれてしまう可能性があるので、とても大事です。

このへんのルールはいろいろあるのですが、とりあえずは、SELECTの後に書いたSUM（集計関数）以外の項目名は、GROUP BYの後にも全部書かないとダメということを覚えておけばいいと思います。

ちなみに、今回はSUMしか使っていないですが、SUMのように、いくつかのデータをまとめて計算するようなものを、SQLでは集計関数と呼んでいます。

平均値を求めるAVG、最大値を求めるMAXなどがあります。

これらももちろん、GROUP BYを使ってグループごとに計算すると効果的です。

デモ用ファイル内にDemo3という画面がありますが、長くなってしまったので、こちらについては次回に回します。