集計クエリー（その５）

今回が集計クエリーの最後になります。最後まで頑張っていきましょう。

１．注文書テーブルで1997/06/20以降で１回の注文で100個以上注文した分又は、1997/06/20以降で単価が300円以上の商品を注文した分の明細を表示。

 

ちょっと条件が複雑なので、整理します。条件は大きく２つに分けられます。

①(1997/06/20以降)　AND　(１回の注文で100個以上注文した分)
日付の条件と数量の条件はAND条件なので、抽出条件欄の横に並べます。

②(1997/06/20以降)　AND　(単価が300円以上の商品を注文した分)
これも日付と単価の条件はAND条件なので、抽出条件欄の横に並べます。

①と②の関係はOR条件なので、縦に並べます。ここでよく間違えるのは②の(1997/06/20以降)を省略してしまう事です。あくまで①と②は独立して判断されるので、②で日付の条件を省略してしまったら、日付に関係なく、単価300円以上のものが全て抽出されてしまいます。

以下が結果になります。



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

 

①まずひと月単位でグループ化する必要があります。単に「日付」でグループ化すると、年月日でグループ化されてしまいますので、Format関数を用いて年月単位のグループ化にします。

②グループ化を階層化します。同一年月で括り、更に会社名が同じものでグループ化します。

③「演算」でSUM関数を用い、月合計を求めます。

④求めた月合計が１万以上を抽出します。

⑤月合計の降順に並べます。

以下が結果になります。

 

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



まず種類でグループ化します。数量の合計、単価の平均、金額の合計を求めます。ついでに並び替えを降順にしておきます。ここまでは今までの知識で出来ると思います。今回のポイントは金額比率を求める方法です。

金額比率を求めるためには種類毎の金額を全体の金額合計で割る必要があります。注文書テーブル全体の金額合計を求めるにはサブクエリーを使います。サブクエリーを使うにはSQLの知識が多少必要になります。

(SELECT SUM([数量]*[単価]) FROM 注文書)

上記の意味は注文書テーブル全体から[数量]*[単価]つまり金額の合計を求めるという事です。サブクエリーはSQL文必ずかっこで囲みます。

小数点以下第２位で四捨五入し、パーセンテージを求めるためには金額比率のプロパティを開き、以下の様に設定します。

 

以下が結果になります。

 

どうでしたか。最後のサブクエリーを使った例はかなり難易度が高い部類に入ると思いますが、このサブクエリーを使いこなせるようになると一段と応用力が増します。特に分析計のレポートを出力しなければならない場合は大いに役立つでしょう。

クエリーに関してはまだまだ様々なパターンがありますが、会得するには演習を積み重ねていくしかありませんので、自分なりに条件をいろいろ想定して結果を確認して見て下さい。

次回からはマクロについて見ていきます。

集計クエリー（その４）

今回も引き続き、集計クエリーの演習を行います。条件が複合的になりますので、少し難しくなります。

１．注文書テーブルの９７年７月における”飲料”及び”乳製品”各々の注文金額合計を求める。

 

グループ化、演算、Where条件が組み合わされた例です。順に見ていきます。

①日付の抽出条件の「９７年７月」を設定するために、日付の集計欄を「Where条件」にします。９７年７月ということは1997/07/01から1997/07/31の事ですから、以下の様にBetween関数を用いて指定します。日付型のフィールドは「#」で囲みます。
Between #開始日付# and #終了日付#

②商品の種類が"飲料"及び"乳製品"という条件もありますので、①と同様に種類の集計欄に「Where条件」を指定します。"飲料"と"乳製品"はどちらでもいいので「OR」で繋ぎます。①と②はAND条件になりますので、抽出条件欄は並列に記述します。もし①と②がOR条件でしたら抽出条件欄を段をずらすだけです。

③種類毎の集計値を見たいので、種類でグループ化します。

④最後に注文金額合計を求める部分ですが、集計欄を「演算」にし、[単価]と[数量]を掛けてそれのSUMをとり、金額合計を求めます。

以下が結果になります。

 

２．注文書テーブルで注文金額合計が10,000円以上の商品を抽出する。

 

１．ではWhere条件で抽出条件を指定しましたが、演算した結果を抽出条件にすることも可能です。商品でグループ化し、その金額合計が10,000円以上で抽出しています。

以下が結果になります。

 

３．注文書テーブルで単価が200円以上の商品の注文合計が10,000円以上の商品を抽出する。

 

抽出条件のAND条件の例です。単価をWhere条件にして「>=200」と指定します。

以下が結果になります。

 

４．注文書テーブルの注文数量の多かった順に、商品を並べ替える。

 

商品でグループ化し、その単位で数量の合計を取り、それを降順に並べます。

以下が結果になります。

 

次回は集計クエリーの演習の最後になります。

集計クエリー（その３）

前回に引き続き集計クエリーの演習をします。

１．成績表テーブルの学期毎に各学科の平均点を求める。但し、Nullは0点とみなす。

 

まず学期でグループ化します。データ中にNULL値が混在していると計算できないので、nz関数を用いて0に変換します。その上でavg関数を用いて平均を求めます。

以下が結果となります。

 

２．成績表テーブルの学期毎に各学科の最高点を求める。



１．と同様まず学期でグループ化します。集計欄を「最大」にして最高点を求めます。

以下が結果となります。

 

３．成績表テーブルの１学期の国語の最高点を取った人の名前を表示する。

 

まずは抽出条件欄に「1」を設定し、国語の点数の降順に並べます。これで国語の点数の高い順に並びますが、最高点の人のみ表示するには「トップ値」プロパティを「1」に設定します。更に氏名を表示するために名簿テーブルと連結して「氏名」を抽出します。「トップ値」プロパティはACCESS独自の機能で「トップ5%」等の設定も可能です。

以下が結果となります。

 

４．成績表テーブルの学期毎の各学科の受験者数を求める。



学期でグループ化し、各学科毎の「カウント」を取るとNULLデータは集計値に含まれません。従って、未受験者は対象外となります。

以下が結果となります。

 

５．成績表テーブルの１学期の受験者数を求める。

 

１学期で抽出し、レコードの件数自体をカウントしたい場合はCount関数を使ってCount(*)とします。Count([国語])とした場合はNULLデータがはじかれるので18件となります。

以下が結果となります。

 

次回では注文書テーブルを使い、更に複雑な集計クエリーに挑戦してみましょう。クエリーの習得するためには数をこなす事が大切です。

集計クエリー（その２）

今回は注文書テーブルを基に、集計クエリーの例題を解いていきます。

１．注文書テーブルの「商品」毎に、数量の合計を求める。

 

まずは商品毎に集計するので、商品フィールドの集計欄を「グループ化」にします。数量フィールドの集計欄は「合計」にします。

以下表示結果です。

 

２．注文書テーブルの「商品」毎に、金額（「数量」×「単価」）の合計を求める。

 

単一のフィールドを基にした「合計」は１．のような指定方法でいいのですが、複数のフィールドを組み合わせた計算など多少複雑な計算の場合は、集計欄は「演算」を指定し、フィールド欄に関数を組み合わせた指定をします。

[数量]*[単価]で金額を求め、SUM関数でその金額の合計を計算します。

以下表示結果です。

 

３．注文書テーブルの年月毎に金額の合計を求める。

 

グループ化するフィールドに単に「日付」を指定した場合は、年月日毎にグルーピングされてしまいます。年月毎にグルーピングしたい場合は、format関数で表示書式を"yyyy/mm"と指定します。

以下表示結果です。

 

次回も引き続き集計クエリーについて見てみます。

演算クエリー（その２）

ACCESSのクエリーではフィールドを抽出する段階でそのフィールドに対して演算を施すことが出来ます。

１．注文書テーブルの「単価」×「数量」で金額を計算する。

 

最も単純な例としてはフィールド同士で算術演算子を用いて四則演算を行うことです。当然のことながら、演算対象になるフィールドは数値型でなければならないし、０除算などを行えばエラーになります。

以下が結果になります。

 

２．注文書テーブルの「単価」と「数量」で計算した金額の全体の平均値を求める。

 

ACCESS標準の関数を使用することも出来ます。主に以下の様な関数があります。

SUM():合計
AVG():平均
MIN():最小
MAX():最大

以下が結果になります。

 

計算結果に端数が生じた場合、端数処理のためにformat()関数を用いる事が出来ます。

format(任意の有効な式,"書式を表す文字列"):第１引数にはフィールド名が指定されることもありますし、この例のように計算式が指定されることもあります。第２引数には出力書式を指定します。数値型のよく使われる書式としてはこの例のような３桁毎のカンマ区切りで先頭の０を抑制したものが使われます。小数点以下は自動的に四捨五入されます。

 

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

 

３．成績表テーブルのNULL値を0に変換して、各行の教科合計を求める。



データ内には点数が入力されていない（テストを受けていない人と見なす。0点は実際に受験して結果0点であることを示す。）人がいて、そのデータはNULL値となっています。このまま演算処理をすると、エラーになります。その為、0に変換して合計を求める必要があります。

NZ():NULL値を0に変換する関数。

以下が結果になります。

 

次回では再度集計クエリーについて例題を解いていきます。