集計クエリー(その5)
今回が集計クエリーの最後になります。最後まで頑張っていきましょう。
1.注文書テーブルで1997/06/20以降で1回の注文で100個以上注文した分又は、1997/06/20以降で単価が300円以上の商品を注文した分の明細を表示。

ちょっと条件が複雑なので、整理します。条件は大きく2つに分けられます。
①(1997/06/20以降) AND (1回の注文で100個以上注文した分)
日付の条件と数量の条件はAND条件なので、抽出条件欄の横に並べます。
②(1997/06/20以降) AND (単価が300円以上の商品を注文した分)
これも日付と単価の条件はAND条件なので、抽出条件欄の横に並べます。
①と②の関係はOR条件なので、縦に並べます。ここでよく間違えるのは②の(1997/06/20以降)を省略してしまう事です。あくまで①と②は独立して判断されるので、②で日付の条件を省略してしまったら、日付に関係なく、単価300円以上のものが全て抽出されてしまいます。
以下が結果になります。
2.注文書テーブルでひと月の注文合計が1万円以上の会社を金額の降順に並べる。
①まずひと月単位でグループ化する必要があります。単に「日付」でグループ化すると、年月日でグループ化されてしまいますので、Format関数を用いて年月単位のグループ化にします。
②グループ化を階層化します。同一年月で括り、更に会社名が同じものでグループ化します。
③「演算」でSUM関数を用い、月合計を求めます。
④求めた月合計が1万以上を抽出します。
⑤月合計の降順に並べます。
以下が結果になります。
3.注文書テーブルで種類毎に数量の合計、単価の平均、金額の合計及び金額比率(全体に対するその種類の金額の割合。小数点第2位で四捨五入)を金額合計の多い順に表示する。
まず種類でグループ化します。数量の合計、単価の平均、金額の合計を求めます。ついでに並び替えを降順にしておきます。ここまでは今までの知識で出来ると思います。今回のポイントは金額比率を求める方法です。
金額比率を求めるためには種類毎の金額を全体の金額合計で割る必要があります。注文書テーブル全体の金額合計を求めるにはサブクエリーを使います。サブクエリーを使うにはSQLの知識が多少必要になります。
(SELECT SUM([数量]*[単価]) FROM 注文書)
上記の意味は注文書テーブル全体から[数量]*[単価]つまり金額の合計を求めるという事です。サブクエリーはSQL文必ずかっこで囲みます。
小数点以下第2位で四捨五入し、パーセンテージを求めるためには金額比率のプロパティを開き、以下の様に設定します。
以下が結果になります。
どうでしたか。最後のサブクエリーを使った例はかなり難易度が高い部類に入ると思いますが、このサブクエリーを使いこなせるようになると一段と応用力が増します。特に分析計のレポートを出力しなければならない場合は大いに役立つでしょう。
クエリーに関してはまだまだ様々なパターンがありますが、会得するには演習を積み重ねていくしかありませんので、自分なりに条件をいろいろ想定して結果を確認して見て下さい。
次回からはマクロについて見ていきます。
1.注文書テーブルで1997/06/20以降で1回の注文で100個以上注文した分又は、1997/06/20以降で単価が300円以上の商品を注文した分の明細を表示。

ちょっと条件が複雑なので、整理します。条件は大きく2つに分けられます。
①(1997/06/20以降) AND (1回の注文で100個以上注文した分)
日付の条件と数量の条件はAND条件なので、抽出条件欄の横に並べます。
②(1997/06/20以降) AND (単価が300円以上の商品を注文した分)
これも日付と単価の条件はAND条件なので、抽出条件欄の横に並べます。
①と②の関係はOR条件なので、縦に並べます。ここでよく間違えるのは②の(1997/06/20以降)を省略してしまう事です。あくまで①と②は独立して判断されるので、②で日付の条件を省略してしまったら、日付に関係なく、単価300円以上のものが全て抽出されてしまいます。
以下が結果になります。

2.注文書テーブルでひと月の注文合計が1万円以上の会社を金額の降順に並べる。

①まずひと月単位でグループ化する必要があります。単に「日付」でグループ化すると、年月日でグループ化されてしまいますので、Format関数を用いて年月単位のグループ化にします。
②グループ化を階層化します。同一年月で括り、更に会社名が同じものでグループ化します。
③「演算」でSUM関数を用い、月合計を求めます。
④求めた月合計が1万以上を抽出します。
⑤月合計の降順に並べます。
以下が結果になります。

3.注文書テーブルで種類毎に数量の合計、単価の平均、金額の合計及び金額比率(全体に対するその種類の金額の割合。小数点第2位で四捨五入)を金額合計の多い順に表示する。

まず種類でグループ化します。数量の合計、単価の平均、金額の合計を求めます。ついでに並び替えを降順にしておきます。ここまでは今までの知識で出来ると思います。今回のポイントは金額比率を求める方法です。
金額比率を求めるためには種類毎の金額を全体の金額合計で割る必要があります。注文書テーブル全体の金額合計を求めるにはサブクエリーを使います。サブクエリーを使うにはSQLの知識が多少必要になります。
(SELECT SUM([数量]*[単価]) FROM 注文書)
上記の意味は注文書テーブル全体から[数量]*[単価]つまり金額の合計を求めるという事です。サブクエリーはSQL文必ずかっこで囲みます。
小数点以下第2位で四捨五入し、パーセンテージを求めるためには金額比率のプロパティを開き、以下の様に設定します。

以下が結果になります。

どうでしたか。最後のサブクエリーを使った例はかなり難易度が高い部類に入ると思いますが、このサブクエリーを使いこなせるようになると一段と応用力が増します。特に分析計のレポートを出力しなければならない場合は大いに役立つでしょう。
クエリーに関してはまだまだ様々なパターンがありますが、会得するには演習を積み重ねていくしかありませんので、自分なりに条件をいろいろ想定して結果を確認して見て下さい。
次回からはマクロについて見ていきます。
スポンサーサイト