SQL基礎問題3：文字列の一部をキーにして集計

公開日：2025-10-29　最終更新日：2025-11-03

SQL基礎問題3：文字列の一部をキーにして集計

文字列の一部分をキーとして件数集計するSQL問題です。

SQLの練習用になるべく単純なSQLで済む問題を出します。
SQLの練習として取り組んで見てください。

問題

※画像はExcelです。

列「ID」は「○○-nnn-mmm」の形式です。
nnn,mmmは桁数不定です。
この時、「○○-nnn」ごとに件数を取得するSQLを作成してください。
※テーブル名は任意
※出力の並び順は問わない。

解答SQL

SQLite3

SELECT
 SUBSTR(ID, 1, INSTR(SUBSTR(ID, INSTR(ID, '-') + 1), '-') + INSTR(ID, '-') - 1) AS pre,
 COUNT(*)
 FROM TBL01
 GROUP BY pre

SQLの解説

「IDの前半部分（2つ目のハイフンまで）ごとの件数を集計するSQL」です。
ID という文字列から2つ目のハイフン（-）の直前までを取り出して pre とし、その pre ごとに行数を数えています。
処理の流れ

INSTR(ID, '-') で最初のハイフンの位置を求めます。
SUBSTR(ID, INSTR(ID, '-') + 1) で、最初のハイフン以降の文字列を取り出します。
その中で INSTR(..., '-') を使い、2つ目のハイフンの位置（相対的な位置）を求めます。
これを最初の位置に足して、「元の文字列中の2つ目のハイフンの位置」を求め、-1 してハイフン直前までを SUBSTR で切り出します。
　→ これが pre 列になります。
最後に GROUP BY pre で同じ pre の行をまとめ、COUNT(*) でそれぞれの件数を数えています。

GROUP BY句でのSELECT句のエイリアス（別名）の使用

GROUP BY句でのSELECT句のエイリアス（別名）の使用は、データベースによってサポート状況が異なります。
MySQLやPostgreSQLなど一部のDBでは使用可能ですが、OracleやSQL Serverなど、多くの主要なDBは標準SQLの仕様に厳密に従うため、GROUP BY句の処理時点ではSELECT句で定義されたエイリアスを参照できず、エラーとなります。
互換性を確保するためには、GROUP BY句にはエイリアスではなく、SELECT句で定義した式全体をそのまま再記述するか、サブクエリを使用することが推奨されます。

Excel

=LET(sql,
"SELECT
 LEFT(ID,InStrRev(ID,'-')-1),COUNT(*)
 FROM ?tbl
 GROUP BY LEFT(ID,InStrRev(ID,'-')-1)",
QUERY(SUBSTITUTE(sql,"?tbl","[TBL01$A1:B11]"),"なし"))

SQLの解説

「IDのうち、最後の区切り（-）より前の部分」で集計するSQL です。
ID の最後のハイフン（-）の直前までを取り出してグループ化し、その値ごとの件数を数える処理です。
処理の流れ

InStrRev(ID, '-')
　→ 文字列の中で「最後のハイフンの位置」を探します。
LEFT(ID, InStrRev(ID, '-') - 1)
　→ ID の先頭から最後のハイフンの1文字前までを取り出します。
　　ハイフン自体は含みません。
GROUP BY でこの部分（pre）ごとにまとめ、
　COUNT(*) で件数を数えます。

※SQLの作成及び解説には生成AIの助けを借りています。特に解説文においてはAI出力を多く使用しています。

同じテーマ「SQL入門」の記事

「ADO + VBA」でSQLを実行するときのVBAサンプル
 SQL基礎問題1：最大在庫数を持つ製品の在庫金額
 SQL基礎問題2：文字列「-nn-」のnnが偶数のみ抽出
SQL基礎問題3：文字列の一部をキーにして集計
SQL基礎問題4：2つのテーブルの不一致を抽出
 SQL基礎問題5：複数のマスタテーブルの結合
 SQL基礎問題6：成績表から教科ごとの点数ベスト3を抽出
 SQL基礎問題7：成績表から各教科の最高点と最低点を抽出
 SQL基礎問題8：バスケット分析・ペア商品の出現回数
 SQL基礎問題9：特定商品購入者の平均購入金額
 SQL基礎問題10：非正規化（カンマ区切り）の結合と集計

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門

ホーム
その他
SQL入門
SQL基礎問題3：文字列の一部をキーにして集計

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.

SQL入門
SQL基礎問題3：文字列の一部をキーにして集計