３．集計

以下のテーブルをもとに、演習を進める。

[ sale ]

sale_id sale_date product_name product_count product_price product_total customer is_delivery note
101 2007/4/1 りんご 20 140 2800 A商店 false (NULL)
102 2007/4/2 いちご 15 400 6000 B商店 true (NULL)
103 2007/5/4 いちご 2 400 800 A商店 false (NULL)
104 2007/5/12 いちじく 32 600 19200 A商店パリ支店 true 空輸
105 2007/5/20 マンゴー 18 260 4680 C商店 false (NULL)
106 2007/6/7 りんご 3 140 420 A商店 false (NULL)
107 2007/7/1 メロン 12 3500 42000 W商店 true のしをつける
108 2007/7/7 七夕セット 3 1780 5340 C商店 false (NULL)
109 2007/7/10 マンゴー 10 260 2600 B商店 false (NULL)
110 2007/8/1 常夏セット 6 2300 13800 海の家 false (NULL)

３－１

配送回数を集計 − 件数

配送回数を表示する。
配送が必要なデータの件数を数え、仮に［is_delivery_count］という列名として表示する。

is_delivery_count
3

キーワード：where、and

【サンプルソースコード】

3-1.sql

/* 3-1．配送回数を集計 − 件数 */ /* 　配送が必要なデータのみを対象に、データ件数を数える */ /* 　countは、特定の列名を指定することも、*を指定することもできる */ select count(*) as is_delivery_count from sale where is_delivery = true;

３－２

金額の平均を計算

１回あたりの平均販売額を、仮に［total_avg］という列名として表示する。

total_avg
9764

キーワード：avg

【サンプルソースコード】

3-2.sql

/* 3-2．金額の平均を計算 */ /* 　avgには特定の列名を指定し、null以外を計算する */ /* 　小数点が気になる場合は、truncate関数が使える */ select truncate(avg(product_total), 0) as total_avg from sale;

３－３

商品毎の売上総数を計算 − グループ毎の合計

商品毎に、全部でいくつ売れたかを集計する。
商品毎に行をまとめ、個数を集計し、それを仮に［countall］という列名として表示する。
下表は[countall]の降順で並べ替えています。

product_name countall
いちじく 32
マンゴー 28
りんご 23
いちご 17
メロン 12
常夏セット 6
七夕セット 3

キーワード：group by、sum

【サンプルソースコード】

3-3.sql

/* 3-3．商品毎の売上総数を計算 − グループ毎の合計 */ /* 　group byは、指定列の同じデータをひとつにまとめる */ /* 　selectには、group byでの指定列か集計関数を指定する */ select product_name, sum(product_count) as countall from sale group by product_name order by countall desc;

３－４

販売先毎の売上回数を集計 − グループ毎の件数

販売先毎の売上回数を集計し、それを仮に［han_count］という列名として表示する。

customer han_count
A商店 3
A商店パリ支店 1
B商店 2
C商店 2
W商店 1
海の家 1

キーワード：group by、count

【サンプルソースコード】

3-4.sql

/* 3-4．販売先毎の売上回数を集計 − グループ毎の件数 */ select customer, count(*) as han_count from sale group by customer;

３－５

販売先毎の売上平均を計算 − グループ毎の平均

各販売先の平均売上金額を、仮に［average］という列名として表示する。

customer average
A商店 1340
A商店パリ支店 19200
B商店 4300
C商店 5010
W商店 42000
海の家 13800

キーワード：group by、avg

【サンプルソースコード】

3-5.sql

/* 3-5．販売先毎の売上平均を計算 − グループ毎の平均 */ select customer, truncate(avg(product_total), 0) as average from sale group by customer;

３－６

金額10000円未満の販売データの回数を集計 − 集計対象の絞り込み

販売先毎に、合計金額が10,000円未満の販売データが何件あるかを表示する。
件数は、仮に［han_count］という列名として表示する。

customer han_count
A商店 3
C商店 2
B商店 2

キーワード：where、group by、count

【サンプルソースコード】

3-6.sql

/* 3-6．金額10000円未満の販売データの回数を集計 − 集計対象の絞り込み */ /* 　まずwhereで10,000円未満のみにして、そこに対してグループ化する */ select customer, count(*) as han_count from sale where product_total < 10000 group by customer;

課題

スポーツクラブの会員データがある。
以下のテーブルをもとに、仕様を満たすようなSQL文を作成する。

[ member ]

member_id regist_date member_name birthday member_gender class member_fee area lesson
M101 2005/2/10 秋田きりん 1978/5/4 男性 マスター 10000 上野 スカッシュ
N511 2006/8/15 長野となかい 1980/10/9 男性 ナイト 8000 上野 (NULL)
M340 2006/8/15 山口ぱんだ 1976/4/28 女性 マスター 10000 上野 ジャズダンス
H028 2006/10/10 長崎らっこ 1979/7/16 男性 ホリデー 7200 品川 スイミング
M502 2007/1/20 石川うさぎ 1976/5/21 女性 マスター 10000 千葉 (NULL)
N113 2007/1/31 岡山ふくろう 1965/12/30 男性 ナイト 8000 上野 (NULL)
D327 2007/3/6 富山ひよこ 1986/4/23 女性 デイ 6500 千葉 (NULL)
N280 2007/4/1 香川ひらめ 1972/6/24 男性 ナイト 8000 品川 (NULL)
H335 2007/4/18 宮崎まんぼう 1968/3/12 男性 ホリデー 7200 品川 (NULL)
M211 2007/4/28 福岡かまきり 1980/10/8 男性 マスター 10000 千葉 ジャズダンス

　

課題3-1．店舗毎の人数を数える。

※店舗名と店舗の所属人数を表示すること
※カラム名は適切な名前に変えること

[結果表示]

店舗 人数
上野 4
千葉 3
品川 3

課題3-2．会員種別毎の人数を数える。

※会員種別名と会員種別に属する人数を表示すること
※カラム名は適切な名前に変えること

[結果表示]

会員種別 人数
デイ 1
ナイト 3
ホリデー 2
マスター 4

課題3-3．店舗に関係なく、レッスン毎の人数を数える。レッスンを受けていない人は表示しない。

※レッスン名と受けている会員の人数を表示すること
※カラム名は適切な名前に変えること

[結果表示]

レッスン 人数
ジャズダンス 2
スイミング 1
スカッシュ 1

課題3-4．店舗毎に、1ヶ月に集める会費の合計を計算する。

※店舗名と合計金額を表示すること
※カラム名は適切な名前に変えること

[結果表示]

店舗 合計金額
上野 36000
千葉 26500
品川 22400

課題3-5．各店舗での、ナイト会員の人数を表示する。

※店舗名と店舗に所属するナイト会員の人数を表示すること
※カラム名は適切な名前に変えること

[結果表示]

店舗 ナイト会員
上野 2
品川 1

課題3-6．各店舗での、男女別人数を数える。

※店舗名と性別ごとの人数が分かるように表示されればよい
※カラム名は適切な名前に変えること

[結果表示 パターン1 ※今回の学習範囲で出来るパターン]

店舗 性別 人数
上野 女性 1
上野 男性 3
千葉 女性 2
千葉 男性 1
品川 男性 3

[結果表示 パターン2 ※学習していない関数を利用するパターン]

店舗 男性 女性
上野 3 1
千葉 1 2
品川 3 0

課題3-7．問い合わせに備えて、会員種別毎に料金を表示する。

※会員種別名と会員種別の料金を表示すること
※カラム名は適切な名前に変えること

[結果表示]

会員種別 料金
デイ 6500
ホリデー 7200
マスター 10000
ナイト 8000

Copyright © Xincor miXell Co., Ltd. All rights reserved

results matching ""

    No results matching ""