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

導入記事にある通りエクセルの神髄さんのSQL基礎問題にチャレンジしていきます。

www.excellovers.com

ExcelでSQLを使方法については上記を参照。

お題

【SQL問題】
列「ID」は「○○-nnn-mmm」の形式です。
nnn,mmmは桁数不定です。
この時、「○○-nnn」ごとに件数を取得するSQLを作成してください。
※テーブル名は任意
※出力の並び順は問わない。 pic.twitter.com/Qgg1WRy5NH
— エクセルの神髄 (@yamaoka_ss) 2025年10月28日

言語化

ピボットテーブルと同じような表現をSQLで実現することになります。

IDの内、2つ目の「-」の手前までを抽出
抽出したIDでグループ化

SQL化

IDからお題である「〇〇ｰnnn」のデータを抽出します。
まずは2つ目の「‐」が何文字目にあるのかを確認します。

INSTRREV関数を利用して位置を確認します。

www.excellovers.com

2つ目の「-」は省きたいので、INSTRREV関数で返ってきた値から１を引いています。

select
  instrrev(ID, '-') - 1 as 何文字目
from
  [Q3$A1:B11]

取得した何文字目かのデータを利用して、IDからグループ化したい箇所を抽出します。
抽出にはLEFT関数.mdを利用します。

www.excellovers.com

Excel関数のLEFTと使い方は同じです。

select
  left (ID, instrrev(ID, '-') - 1) as ID抽出 
from
  [Q3$A1:B11]

最後に抽出したIDを利用してデータをグループ化します。
データのグループ化はGROUP BY句を利用します。

www.excellovers.com

select
  left (ID, instrrev(ID, '-') - 1) as ID
  , count(*) as 件数 
from
  [Q3$A1:B11] 
group by
  left (ID, instrrev(ID, '-') - 1)

WITH句の代わり

上記の最終解答のSQLですが、「left (ID, instrrev(ID, '-') - 1)」という表記が2回出てきていて、少し野暮ったい感じがしますね。

こういう時にSQLではWITH句という構文を利用して記述を省略することが出来ます。
構文としてはWITH句でSQL文を記載して名前を付けておくと、SQL本文で設定した名前でSQLを作成することが出来ます。
VBAでいう変数のように使うことが出来ます。

WITH CTE名 AS (
    SELECT ... 
)
SELECT *
FROM CTE名
WHERE ...;

便利な機能ですが、残念ながらExcel × ADOではWITH句を利用することが出来ません。
その代わりと言ってはなんですが、ExcelのLET関数を使用することで同じように記載することが出来ます。

=LET(ID," left (ID, instrrev(ID, '-') - 1)",
kfSQLSearch("
select
  " & ID & " as ID
  , count(*) as 件数 
from
  [Q3$A1:B11] 
group by
  " & ID & ""
))