SQL Serverでグループ化・累積合計・グループ合計を実装するT‑SQL実践ガイド（varchar価格の変換エラー対策付き）

SQL Server / T‑SQL でカテゴリ別の合計や累積合計を出したいのに、元データが XML 内の varchar 文字列で「Error converting data type varchar to numeric」が出てしまう──そんな場面は珍しくありません。本記事では、価格の正規化からグループ集計・Running Total・グループ合計の表示まで、実務でそのまま使える T‑SQL パターンをまとめて解説します。

SQL Server での「グループ化」「累積合計」「グループ合計」を整理する

まず、本記事でゴールとする集計イメージをはっきりさせておきます。前提となるテーブル構造は次のようなものとします。

テーブル名：MyTable
主な列：Category, Unit, SerialNo, XmlData
価格：dbo.xtag('Price', XmlData) で XML から取り出す（型は varchar）
並び順：SerialNo（行の時系列や明細順を表すキー）

やりたいことは、次の 3 つです。

カテゴリ × 単位ごとにグループ化して合計を出す
グループ内の行に対して累積合計（Running Total）を計算する
各行に累積合計を出しつつ、グループ全体の合計も表示する
（必要に応じて「最終行だけグループ合計を表示」する）

さらに、本記事では多くの人を悩ませる次のエラーにもきちんと対処します。

Error converting data type varchar to numeric.

これは、XML から取り出した価格が varchar であり、数値変換できない「変な文字列」が混ざっている場合に発生します。したがって、実務的には次の 2 ステップで考えるのが安全です。

ステップ 1：価格文字列を decimal に正規化する共通処理を作る
ステップ 2：その結果を使って GROUP BY やウィンドウ関数で集計する

前処理：XML の価格文字列を decimal に正規化する

最初に、どの集計クエリからも使い回せる「共通 CTE」を用意します。ここでは、桁区切りのカンマ付きの価格を想定し、数値に変換できない値は NULL に落とします。

-- 共通CTE: 文字列の価格を数値(decimal)に正規化
WITH src AS (
  SELECT
    Category,
    Unit,
    SerialNo,
    TRY_CONVERT(decimal(18,2),
      REPLACE(dbo.xtag('Price', XmlData), ',', '')
    ) AS PriceNum
  FROM MyTable
)

TRY_CONVERT を使うことで、変換できない文字列が来てもエラーにならず NULL になります。CONVERT や CAST だと例外が発生してクエリ自体が失敗するため、ログや不正データの洗い出しをしながら集計したいときには TRY_ 系関数が非常に便利です。

文字列の例	TRY_CONVERT(decimal(18,2), …)	CONVERT(decimal(18,2), …)
`'1,234.56'`	1234.56 に変換	カンマ除去なしならエラー
`'ABC'`	`NULL`（エラーにならない）	エラー（クエリ失敗）
`NULL`	`NULL`	`NULL`

以降のすべてのサンプルクエリでは、この src CTE を使って PriceNum（decimal 型の価格）を参照する形に統一します。

サンプルデータで集計イメージを確認する

説明をわかりやすくするため、簡単なサンプルデータを頭の中に描いておきます。実際には XML から価格を取り出しますが、ここでは展開後のイメージだけ示します。

Category	Unit	SerialNo	Price（文字列）
A	個	1	1,000
A	個	2	2,000
A	箱	3	5,000
B	個	4	abc（誤入力）

上のようなデータに対して、カテゴリ × 単位の合計、Running Total、グループ合計をどのように出していくかを見ていきます。

カテゴリ × 単位でグループ化して合計を出す

まずは一番ベーシックな「グループごとの合計」のクエリです。src CTE を使って、Category と Unit を単純に GROUP BY します。

WITH src AS (
  SELECT Category, Unit,
         TRY_CONVERT(decimal(18,2),
           REPLACE(dbo.xtag('Price', XmlData), ',', '')
         ) AS PriceNum
  FROM MyTable
)
SELECT
  Category,
  Unit,
  SUM(PriceNum) AS グループ合計
FROM src
GROUP BY Category, Unit
ORDER BY Category, Unit;

このクエリの結果イメージは、先ほどのサンプルなら次のようになります。

Category	Unit	グループ合計
A	個	3000
A	箱	5000
B	個	NULL（誤入力は 0 と見なさない）

TRY_CONVERT のおかげで、「abc」のような誤入力は NULL になり、合計には影響しません。「不正なものは 0 とみなす」のか「NULL のままにするのか」は設計次第ですが、まずは NULL のままにして、別途不正データを洗い出すのがオススメです。

ウィンドウ関数で累積合計（Running Total）を出す

次に、「カテゴリ × 単位」ごとのグループ内で、行の順番に従って累積合計を出します。ここで活躍するのが、SQL Server のウィンドウ関数です。

WITH src AS (
  SELECT Category, Unit, SerialNo,
         TRY_CONVERT(decimal(18,2),
           REPLACE(dbo.xtag('Price', XmlData), ',', '')
         ) AS PriceNum
  FROM MyTable
)
SELECT
  Category,
  Unit,
  SerialNo,
  PriceNum,
  SUM(PriceNum) OVER (
    PARTITION BY Category, Unit
    ORDER BY SerialNo
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS 累積合計
FROM src
ORDER BY Category, Unit, SerialNo;

SUM(PriceNum) OVER (...) の部分がポイントです。

PARTITION BY Category, Unit：カテゴリ × 単位ごとに集計範囲を区切る
ORDER BY SerialNo：グループ内での行の順序（時系列など）を定義
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：「最初の行から現在の行まで」の範囲を指定（＝Running Total）

サンプルデータの Category=A, Unit=個の部分だけ抜き出して結果イメージを見ると、次のようになります。

Category	Unit	SerialNo	PriceNum	累積合計
A	個	1	1000	1000
A	個	2	2000	3000

ただし、ここで 1 つ注意点があります。ORDER BY SerialNo のキーは一意になるようにしておくことです。もし SerialNo が重複しうるなら、次のように複数のキーを指定して、順序が決定的になるようにしておきましょう。

ORDER BY SerialNo, Category, Unit

または、テーブル側で IDENTITY や ROW_NUMBER で一意な行番号を振っておくのも実務ではよく使われる手です。

累積合計とグループ合計を同時に表示する

実務では「明細の行ごとに Running Total を出しつつ、グループ全体の合計も一緒に見たい」というニーズが多いです。ここでは 2 パターン紹介します。

A. 各行に同じグループ合計を出す（見やすさ重視）

最もシンプルなのは、各行に「グループ合計」として同じ値を持たせる形です。ウィンドウ関数をもう 1 回使うだけで実現できます。

WITH src AS (
  SELECT Category, Unit, SerialNo,
         TRY_CONVERT(decimal(18,2),
           REPLACE(dbo.xtag('Price', XmlData), ',', '')
         ) AS PriceNum
  FROM MyTable
)
SELECT
  Category,
  Unit,
  SerialNo,
  PriceNum,
  SUM(PriceNum) OVER (
    PARTITION BY Category, Unit
    ORDER BY SerialNo
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS 累積合計,
  SUM(PriceNum) OVER (
    PARTITION BY Category, Unit
  ) AS グループ合計
FROM src
ORDER BY Category, Unit, SerialNo;

結果イメージは次のようになります。

Category	Unit	SerialNo	PriceNum	累積合計	グループ合計
A	個	1	1000	1000	3000
A	個	2	2000	3000	3000

BI ツールや Excel で可視化する場合にも扱いやすく、コードも読みやすいので、特別な事情がなければこのパターンを第一候補にすると良いでしょう。

B. グループの最終行にだけ合計を出す（行数を抑えたい場合）

レポートの行数を減らしたい場合、「グループの最終行だけに合計を表示したい」ということもあります。このときは、LEAD 関数で「次の行があるかどうか」を判定するのがスマートです。

WITH src AS (
  SELECT Category, Unit, SerialNo,
         TRY_CONVERT(decimal(18,2),
           REPLACE(dbo.xtag('Price', XmlData), ',', '')
         ) AS PriceNum
  FROM MyTable
)
SELECT
  Category,
  Unit,
  SerialNo,
  PriceNum,
  SUM(PriceNum) OVER (
    PARTITION BY Category, Unit
    ORDER BY SerialNo
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS 累積合計,
  CASE
    WHEN LEAD(SerialNo) OVER (
           PARTITION BY Category, Unit
           ORDER BY SerialNo
         ) IS NULL
      THEN SUM(PriceNum) OVER (PARTITION BY Category, Unit)
    ELSE NULL
  END AS グループ合計_最終行のみ
FROM src
ORDER BY Category, Unit, SerialNo;

LEAD(SerialNo)... IS NULL となるのは、「次の行が存在しない＝グループの最後の行」です。その行にだけ、SUM(PriceNum) OVER (PARTITION BY Category, Unit) の結果（グループ合計）を表示し、それ以外は NULL にしています。

文字列として空欄を出したい場合には、次のようにキャストしてもかまいません。

CASE
  WHEN LEAD(SerialNo) OVER (PARTITION BY Category, Unit ORDER BY SerialNo) IS NULL
    THEN CAST(SUM(PriceNum) OVER (PARTITION BY Category, Unit) AS varchar(50))
  ELSE ''
END AS グループ合計_最終行のみ

実務的には、SQL 内では数値のまま扱い、出力形式だけアプリケーション側で変える方がメンテナンス性は高くなります。レポート用にどうしても列型を変えたい場合だけ、上記のように文字列化するとよいでしょう。

「varchar を numeric に変換できません」エラーの原因と対処

ここからは、冒頭で触れた Error converting data type varchar to numeric の対処をもう少し掘り下げます。代表的な原因は次のようなものです。

桁区切りのカンマが残っている（例：'1,234'）
通貨記号や単位が付いている（例：'¥1,000'、'1000円'）
括弧付きマイナス表記（例：'(1,000)' → マイナス 1000 を表す）
純粋に誤入力（例：'ABC'、'一万'）

変換できない値を洗い出すクエリ

まずは「どんな値が変換できていないのか」を把握するためのクエリを用意しましょう。

SELECT DISTINCT p
FROM (
  SELECT dbo.xtag('Price', XmlData) AS p
  FROM MyTable
) s
WHERE TRY_CONVERT(decimal(18,2), REPLACE(p, ',', '')) IS NULL
      AND p IS NOT NULL
      AND p &lt;&gt; '';

このクエリは「カンマを除去したうえで decimal に変換できない値」を一覧してくれます。結果を見ながら、通貨記号や括弧、単位など、何をどう除去すべきかを決めていきます。

通貨記号・括弧付きマイナスへの対応例

通貨記号や括弧付きマイナスが混在する場合の一例を示します。

WITH raw AS (
  SELECT dbo.xtag('Price', XmlData) AS p
  FROM MyTable
)
SELECT
  CASE
    WHEN p LIKE '(%' AND p LIKE '%)'
      THEN -1 * TRY_CONVERT(decimal(18,2),
             REPLACE(REPLACE(REPLACE(p, '(', ''), ')', ''), ',', '')
           )
    ELSE TRY_CONVERT(decimal(18,2),
           REPLACE(REPLACE(p, '$', ''), ',', '')
         )
  END AS PriceNum
FROM raw;

元の文字列 `p`	処理後の `PriceNum`	説明
`'1,234'`	1234.00	カンマ除去 → decimal 変換
`'$5,000'`	5000.00	`$` とカンマを除去
`'(1,000)'`	-1000.00	括弧を除去し、マイナスを掛ける

実際のシステムでは、使用される表記ルールを整理し、共通の正規化ロジックとして関数化するとメンテナンス性が格段に上がります（例：dbo.NormalizePrice(varchar) のような UDF）。

XML から直接 decimal 型で取り出す方がより堅牢

これまでの例では、dbo.xtag('Price', XmlData) が varchar を返す前提で話を進めてきました。しかし、XML のスキーマがわかっているなら、最初から数字として取り出す方が根本的には安全です。

XML 型の列 XmlData から、<Price> 要素の値を decimal として取得する例を示します。

SELECT
  Category,
  Unit,
  SerialNo,
  XmlData.value('(//Price/text())[1]', 'decimal(18,2)') AS PriceNum
FROM MyTable;

(//Price/text())[1] は「Price 要素のテキストの先頭 1 件」を意味する XQuery 式です。
XML の構造によっては //Price ではなく、/Root/Item/Price のようにパスを絞り込んだ方が高速になります。

可能であれば、dbo.xtag 側を改修して decimal 型を返すようにする、または XML のパスと型を明示して .value(...) で直接取り出すようにしましょう。これにより、文字列の前処理や変換エラーに悩まされる頻度が大きく下がります。

パフォーマンスのための設計ポイント

ウィンドウ関数や集計を大量データに対して行う場合、インデックス設計も重要になってきます。特に意識したいのは次の 2 点です。

PARTITION BY / ORDER BY に合わせた複合インデックス

累積合計のクエリでは、PARTITION BY Category, Unit ORDER BY SerialNo のように複数列を使っています。このパターンで多用するなら、次のような複合インデックスを検討しましょう。

CREATE INDEX IX_MyTable_Category_Unit_SerialNo
ON MyTable (Category, Unit, SerialNo);

これにより、SQL Server はインデックスだけでグループ内の並びを決定できるようになり、ソート処理やワークテーブルの利用を減らせます。

価格を数値列として持てるならベスト

もしアプリケーション側の変更が許されるなら、そもそも価格を XML 内ではなく、普通の数値列（decimal）としてテーブルに持つのがベストです。XML は拡張性の面で便利ですが、集計が多い列まで XML に押し込んでしまうと、パフォーマンスやメンテナンス性の観点から後々まで尾を引きます。

既存システムではすぐに変えられないことも多いですが、「将来のリファクタリング候補」として頭の片隅に置いておくと良いでしょう。

実務でハマりがちなポイントまとめ

最後に、この記事で扱った内容を実務の観点から整理しておきます。

テーマ	ありがちな落とし穴	回避策・ベストプラクティス
価格文字列の変換	直接 `CAST` / `CONVERT` してエラー	`TRY_CONVERT` を使い、カンマや通貨記号を事前に除去する
Running Total	`ORDER BY` があいまいで結果が安定しない	一意なキーまで含めて `ORDER BY` する、行番号列を用意する
グループ合計表示	集計用と明細用で似たクエリを大量に作ってしまう	ウィンドウ関数を活用し、1 本のクエリで「累積」と「グループ合計」を同時に出す
不正データ	変換エラーを無理やり 0 扱いしてしまう	まずは NULL にしておき、別クエリで不正な文字列を洗い出す
パフォーマンス	インデックス不備でソートが高コストになる	`PARTITION BY / ORDER BY` に合わせて複合インデックスを設計する

まとめ：SQL Server でのグループ集計と累積合計の定石

本記事で紹介した T‑SQL のパターンを整理すると、次のようになります。

グループ合計：
GROUP BY Category, Unit と SUM(PriceNum) でシンプルに取得。
累積合計（Running Total）：
SUM(PriceNum) OVER (PARTITION BY Category, Unit ORDER BY SerialNo ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
グループ合計を各行に表示：
SUM(PriceNum) OVER (PARTITION BY Category, Unit)
最終行だけグループ合計：
LEAD＋CASE で最終行を判定し、その行だけウィンドウ集計結果を表示。
varchar → numeric 変換エラー対策：
TRY_CONVERT を使い、カンマ・通貨記号・括弧などを事前に除去。可能なら XML から直接 decimal で取得する。

これらのパターンを一度自分のプロジェクト用にテンプレート化しておくと、似たような集計要件が来たときにほぼコピペで対応できるようになります。特に、Running Total やグループ合計の書き方は「覚えてしまえば使い回しが効く」典型的な T‑SQL の定石です。ぜひ自分のデータに置き換えて、実際に手を動かして確認してみてください。

この記事を書いた人

佐藤健司

実務の現場で詰まりがちなポイントを地図にするITブログ「IT trip」を運営。Windows／Office（Teams・Excel）からSQL、サーバ運用、ガジェットまで、再現性のある手順と“なぜそうなるか”を丁寧に解説します。読んだらすぐ試せること、そして迷った人の次の一歩が見えることを大切にしています。

SQL Serverでグループ化・累積合計・グループ合計を実装するT‑SQL実践ガイド（varchar価格の変換エラー対策付き）

SQL Server での「グループ化」「累積合計」「グループ合計」を整理する

前処理：XML の価格文字列を decimal に正規化する

サンプルデータで集計イメージを確認する

カテゴリ × 単位でグループ化して合計を出す

ウィンドウ関数で累積合計（Running Total）を出す

累積合計とグループ合計を同時に表示する

A. 各行に同じグループ合計を出す（見やすさ重視）

B. グループの最終行にだけ合計を出す（行数を抑えたい場合）

「varchar を numeric に変換できません」エラーの原因と対処

変換できない値を洗い出すクエリ

通貨記号・括弧付きマイナスへの対応例

XML から直接 decimal 型で取り出す方がより堅牢

パフォーマンスのための設計ポイント

PARTITION BY / ORDER BY に合わせた複合インデックス

価格を数値列として持てるならベスト

実務でハマりがちなポイントまとめ

まとめ：SQL Server でのグループ集計と累積合計の定石

この記事を書いた人

コメント

コメントするコメントをキャンセル

SQL Serverでグループ化・累積合計・グループ合計を実装するT‑SQL実践ガイド（varchar価格の変換エラー対策付き）

SQL Server での「グループ化」「累積合計」「グループ合計」を整理する

前処理：XML の価格文字列を decimal に正規化する

サンプルデータで集計イメージを確認する

カテゴリ × 単位でグループ化して合計を出す

ウィンドウ関数で累積合計（Running Total）を出す

累積合計とグループ合計を同時に表示する

A. 各行に同じグループ合計を出す（見やすさ重視）

B. グループの最終行にだけ合計を出す（行数を抑えたい場合）

「varchar を numeric に変換できません」エラーの原因と対処

変換できない値を洗い出すクエリ

通貨記号・括弧付きマイナスへの対応例

XML から直接 decimal 型で取り出す方がより堅牢

パフォーマンスのための設計ポイント

PARTITION BY / ORDER BY に合わせた複合インデックス

価格を数値列として持てるならベスト

実務でハマりがちなポイントまとめ

まとめ：SQL Server でのグループ集計と累積合計の定石

この記事を書いた人

関連記事

コメント

コメントする コメントをキャンセル

コメントするコメントをキャンセル