グループごとに1つの数字
GROUP BY が同じ値を持つ行を集め、集計関数がそれぞれの山を1つの数字に潰します。
1
SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status
ORDER BY orders DESC;結果 — 7 行のうち先頭 5 行
| status | orders |
|---|---|
| delivered | 1351 |
| paid | 695 |
| shipped | 641 |
| pending | 349 |
| refunded | 327 |
ブラウザの別タブで Planche が開き、サンプルデータベースを読み込んだ状態でこのクエリが入ります。このページの例はどれも同じタブを使い回します。F9 を押すまで実行はされません。
ステータスが7種類なので7行です。SELECT に書ける列は、グループ化に使った列か、集計した値のどちらかに限られます。これが結果を一意に決めるための規則です。ここで order_number を要求したら、質問自体が意味を失います。配達済みの1,351件のうち、どの注文番号を返せばいいのでしょうか。
COUNT(*) と COUNT(列) は違う
2
SELECT COUNT(*) AS customers,
COUNT(phone) AS with_phone,
COUNT(*) - COUNT(phone) AS without_phone
FROM customers;結果 — 1 行
| customers | with_phone | without_phone |
|---|---|---|
| 1500 | 1221 | 279 |
COUNT(*) は行を数えます。COUNT(phone) は phone が NULL でない行を数えます。差は絞り込みのレッスンに出てきた279人で、これは実用的な技でもあります。2つの差を取れば、2本目のクエリなしに「欠けている数」が分かります。
他の集計関数も NULL を飛ばします。いちばん効いてくるのは AVG です。10、20、NULL の平均は10ではなく15です。NULL はゼロとして扱われるのではなく、そもそも数に入りません。ゼロとして扱ってほしいなら COALESCE(列, 0) と書いて、そう指示します。
複数の集計をいっぺんに
3
SELECT cat.name AS category,
COUNT(*) AS products,
ROUND(MIN(p.price), 2) AS cheapest,
ROUND(AVG(p.price), 2) AS average,
ROUND(MAX(p.price), 2) AS dearest
FROM products p
JOIN categories cat ON cat.id = p.category_id
GROUP BY cat.id
ORDER BY average DESC
LIMIT 20;結果 — 20 行のうち先頭 5 行
| category | products | cheapest | average | dearest |
|---|---|---|---|---|
| Wearables | 11 | 417.3 | 1672.29 | 2433.73 |
| Programming | 10 | 6.89 | 1585.67 | 2420.02 |
| Tablets | 17 | 314.76 | 1529.45 | 2412.93 |
| Audio | 11 | 593.8 | 1482.89 | 2334.28 |
| Kitchen | 12 | 670.54 | 1443.21 | 2293.8 |
テーブルを1回なめるだけで、カテゴリごとに5つの問いに答えています。同じことをアプリ側でやるなら、400行の商品データを全部転送してループを回すことになります。データベースは行がある場所で5つの数字を計算し、20行だけを返します。
結合をまたいで集計する
集計と結合は組み合わせられます。まず結合し、結合後の行をグループ化します。
4
SELECT cat.name AS category,
COUNT(DISTINCT o.id) AS orders,
SUM(oi.quantity) AS units,
ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.status IN ('paid', 'shipped', 'delivered')
GROUP BY cat.id
ORDER BY revenue DESC;結果 — 27 行のうち先頭 5 行
| category | orders | units | revenue |
|---|---|---|---|
| Watches | 395 | 815 | 933900.92 |
| Board Games | 395 | 801 | 929534.55 |
| Laptops | 366 | 768 | 900778.09 |
| Science | 340 | 709 | 888542.78 |
| Cycling | 405 | 823 | 858478.88 |
WHERE は前、HAVING は後
どちらも絞り込みです。違うのは絞り込む時点だけで、違いはそれだけです。
5
SELECT cat.name AS category,
ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM order_items oi
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
GROUP BY cat.id
HAVING SUM(oi.quantity * oi.unit_price) > 1000000
ORDER BY revenue DESC;結果 — 8 行のうち先頭 5 行
| category | revenue |
|---|---|
| Board Games | 1377454.99 |
| Tablets | 1348572.94 |
| Science | 1347046.09 |
| Watches | 1325717.58 |
| Cycling | 1288143.46 |
WHERE はグループ化の前に動き、どの行が参加するかを決めます。HAVING は後で動き、どのグループが残るかを決めます。だから生の列に対する条件は WHERE に、集計に対する条件は HAVING にしか書けません。WHERE が動いている時点では、集計はまだ存在しないからです。
SELECT のレッスンの句の順序を並べてみれば、規則はひとりでに説明されます。
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT計算した値でグループ化する
グループ化できるのは列だけではなく、式でもかまいません。タイムスタンプの先頭7文字を切り出せば 2025-01、つまり月になります。
6
SELECT substr(ordered_at, 1, 7) AS month,
COUNT(*) AS orders,
ROUND(SUM(total), 2) AS revenue
FROM orders
WHERE status IN ('paid', 'shipped', 'delivered')
AND ordered_at >= '2025-01-01'
GROUP BY month
ORDER BY month;結果 — 12 行のうち先頭 5 行
| month | orders | revenue |
|---|---|---|
| 2025-01 | 52 | 311707.73 |
| 2025-02 | 53 | 350775.62 |
| 2025-03 | 58 | 351184.96 |
| 2025-04 | 52 | 374465.94 |
| 2025-05 | 53 | 381541.09 |
こうして導いた値でまとめるのが、「月次」「週次」レポートのほぼすべての書き方です。GROUP BY month が SELECT の別名を使い回している点にも注目してください。GROUP BY と ORDER BY ではどのエンジンもこれを許します。厳しいのは WHERE と HAVING だけです。
ここまでで書けるもの
レポートが作れるようになりました。まだできないのは、1行を要約と突き合わせることです。「平均より高い商品」「カテゴリごとの上位3件」。これには2つのクエリを同時に走らせる必要があり、次のレッスンで扱います。