真の力は、これまでに学んだことを組み合わせることで発揮されます。2つのテーブルをジョインし、結合した行をグループ化し、各グループを集計関数で要約することです。
一般的なレポートとして「各顧客はどれだけの金額を消費したか?」があります。顧客とその注文をジョインし、顧客ごとにグループ化して、SUM で合計を算出します。
SELECT customer.name,
COUNT(*) AS orders,
ROUND(SUM(orders.total), 2) AS spent
FROM customer
INNER JOIN orders ON customer.id = orders.customer_id
GROUP BY customer.id, customer.name
ORDER BY customer.name;
順に読み解くと:テーブルをジョインし、顧客ごとに行をグループ化し、その後各グループについて注文数をカウントし、合計を足し合わせます。
INNER JOIN は注文がない Ben と Dan を除外します。LEFT JOIN は各顧客を保持し、一致しない注文に対して NULL を提供します。COUNT(*) はそのプレースホルダー行を1つとしてカウントしますが、COUNT(orders.id) は NULL id をスキップして正しくゼロを返します。SUM は非 NULL の合計がない場合に NULL を返すため、0 支出レポートには COALESCE(..., 0) を使用します。
25 のみをカウントし、かつすべての顧客を保持するには、条件を ON に設定します。
SELECT customer.name, COUNT(orders.id) AS qualifying_orders,
COALESCE(ROUND(SUM(orders.total), 2), 0) AS spent
FROM customer
LEFT JOIN orders ON customer.id = orders.customer_id AND orders.total >= 25
GROUP BY customer.id, customer.name
ORDER BY customer.name;
結果は Ada: 1 と 25; Ben: 0 と 0; Cara: 1 と 40; Dan: 0 と 0 です。orders.total >= 25 を WHERE に移動すると、Ben と Dan が削除されます。彼らの NULL 合計はその比較を満たしません。同じ名前を持つ顧客が別々に残るよう、id と名前でグループ化してください。
- まずジョインを行い、次に
GROUP BYを用いて結合した行を要約します。 - 両方のテーブルで同じカラムがある場合は、カラム名にテーブル名を付与します。
テーブルのジョイン
インナージョインでは、一致する行がない行は除外される。
難易度 4/5 · 280 コイン + 今日初回達成時の小ボーナス
注文を持つ各顧客について、顧客name、注文数orders、総消費額(小数点以下2桁:spent)を表示する。ジョインし、customer.id でグループ化、name で並べ替える。
実行ボタンをクリックして出力を確認してください。
難易度 4/5 · 280 コイン + 今日初回達成時の小ボーナス
INNER JOIN は注文のないベンとダンを除外した。LEFT JOIN を使用して全顧客を保持し、COUNT(orders.id) を用いて注文のない顧客には0 を表示する。name で並べ替える。
実行ボタンをクリックして出力を確認してください。
難易度 4/5 · 280 コイン + 今日初回達成時の小ボーナス
該当注文のない顧客も含め、全顧客を表示する。name、total > 30 を orders とする注文の数、および合計金額を spent として表示する(2 桁の小数点で四捨五入、または none の場合は 0)。氏名順に並べ替える。未一致の顧客が残るようフィルタを配置する。
実行ボタンをクリックして出力を確認してください。