SQLを使用して、テーブルのカラムを合計して縦列を入れ替えて取得したい。

Question

質問させていただきます。

DB上に、下記のようなsample_tableテーブルがあると想定します。

type	cost
A	10
A	100
A	200
B	50
B	10
C	10
C	20
C	30
C	40

このテーブルに対して、下記のような形で結果を取得できるSQL文を作成したいのですが、うまく行かずに詰まっています。

A	B	C	D
310	60	100	0

type毎にカラムが分けられ、costの合計が値になります。
カラム名の A B C D は固定（クエリ内に直接指定しても良い）の想定です。
もとの sample_table には Dというtypeがないため、そのcostの合計は0となります。

イメージとしては、下記クエリの結果の行と列を入れ替えて、かつ D という元のテーブルにないtypeの total_cost は0になるというものです。

SELECT
  type,
  SUM(cost) AS total_cost
FROM sample_table
GROUP BY type

アドバイスいただければ幸いです。
よろしくお願いいたします。

Answer 1

DBMSが不明なので確実なことは言えないのですが、たとえば以下のようなクエリであれば、多くのDBMSで希望の結果を得ることができると思います。

SELECT
  COALESCE(SUM(CASE WHEN type = 'A' THEN cost END), 0) AS A,
  COALESCE(SUM(CASE WHEN type = 'B' THEN cost END), 0) AS B,
  COALESCE(SUM(CASE WHEN type = 'C' THEN cost END), 0) AS C,
  COALESCE(SUM(CASE WHEN type = 'D' THEN cost END), 0) AS D
FROM sample_table

SQL ServerやOracleのようにPIVOTがサポートされているDBMSであれば以下のようなクエリでも実現できると思います。

SELECT 
  COALESCE(A, 0) AS A,
  COALESCE(B, 0) AS B,
  COALESCE(C, 0) AS C,
  COALESCE(D, 0) AS D
FROM sample_table
PIVOT (SUM(cost) FOR type IN (A, B, C, D)) AS T;

Comments

[takanaweb5]

COALESCEを使用しなくても
CASE文でelse 0 とすれば、nullを0に出来ると思います。

SELECT
SUM(CASE WHEN type = 'A' THEN cost ELSE 0 END) AS A,
SUM(CASE WHEN type = 'B' THEN cost ELSE 0 END) AS B,
SUM(CASE WHEN type = 'C' THEN cost ELSE 0 END) AS C,
SUM(CASE WHEN type = 'D' THEN cost ELSE 0 END) AS D
FROM sample_table

[itouuuuuuuuu]

ご指摘の方法で動作させることができました。
大変参考になりました。
ありがとうございます！

Answer 2

SELECTの前に

SELECT 'D' AS TYPE
     , 0 AS COST
FROM DUAL
UNION ALL

を追加しては？
(Oracle Database での記述方法、同じSQLでもデータベースやバージョンによって方言が大きいのでデータベースやバージョン)は明記できた方適切なコメント付きやすいです。

Comments

[itouuuuuuuuu]

なるほど、UNIONで追加するという方法もありそうですね！
アドバイスありがとうございます。
参考にさせていただきます。