SQL サブクエリを使用してクロス集計をしたい

Question

タイトル通りのことをしたいのですが、組み合わせ方がわかりません。

一つ目のテーブル

INSERT INTO 
  sql_task.toys
  (id,name,colorcode,price,remarks)
  VALUES 
  (1, 'パーティ衣装', "002", 1680,NULL),
  (2, 'ブリキのおもちゃ', "001", 412,NULL),
  (3, 'ゾンビのマスク', "005", 568,NULL),
  (4, 'ブリキのおもちゃ', "004", 412,NULL),
  (5, 'ゾンビのマスク', "002", 568,NULL),
  (6, 'ブリキのおもちゃ',  "005", 412,NULL),
  (7, 'ゾンビのマスク', "001", 568,NULL),
  (8, 'ゾンビのマスク', "004", 568,NULL),
  (9, 'パーティ衣装', "001", 1680,NULL),
  (10, 'パーティ衣装', "004", 1680,NULL),
  (11, 'パーティ衣装',  "005", 1680,NULL),
  (12, 'ブリキのおもちゃ', "001", 412,NULL),
  (13, 'パーティ衣装', "001", 1680,NULL),
  (14, 'ゾンビのマスク', "004", 568,NULL),
  (15, 'パーティ衣装', "003", 1680,NULL),
  (16, 'ブリキのおもちゃ', "003", 412,NULL),
  (17, 'ブリキのおもちゃ', "003", 412,NULL),
  (18, 'パーティ衣装', "003", 1680,NULL),
  (19, 'ブリキのおもちゃ', "002", 412,NULL),
  (20, 'ゾンビのマスク', "001", 568,NULL);

2つ目のテーブル

INSERT INTO 
  sql_task.colortable
  (id,colorcode,color)
  VALUES 
  (1,"001","赤"),
  (2, "002", "青"),
  (3, "003", "黄"),
  (4, "004", "白"),
  (5, "005", "黒");

上記２つを結合したテーブル

SELECT
  T.id,
  T.name AS "商品名",
  C.color AS "色",
  T.price AS "値段"
FROM
  toys AS T
RIGHT JOIN
  colortable AS C
ON
  T.colorcode = C.colorcode;

結合済みのテーブルをサブクエリとし、
それを使用してクロス集計を行います

クロス集計の結果は下記のように、色がそれぞれ何回出たかカウントした集計になってほしいです。

例：
id 商品名　　赤　青　　白　　黄色
1　　あああ　 1　　３　　４　　１
2　　いいい　２　　４　　０　　２
3　　ううう　　
4　　えええ　　

とりあえず色々なサイトから引っ張ってきてコードを下記のように書いてみたのですが、'field list'にないよエラーが出てしまい詰まっています。

SELECT name,
  max(CASE WHEN color = '赤' THEN 1 ELSE 0 END) AS 赤,
  max(CASE WHEN color = '青' THEN 1 ELSE 0 END) AS 青,
  max(CASE WHEN color = '黄' THEN 1 ELSE 0 END) AS 黄,
  max(CASE WHEN color = '白' THEN 1 ELSE 0 END) AS 白,
  max(CASE WHEN color = '黒' THEN 1 ELSE 0 END) AS 黒
FROM (
  SELECT
  T.id,
  T.name AS "商品名",
  C.color AS "色",
  T.price AS "値段"
FROM
  toys AS T
RIGHT JOIN
  colortable AS C
ON
  T.colorcode = C.colorcode) AS toys2
GROUP BY name;

ご教授よろしくお願いいたします！

Answer 1

'field list'にないよエラーが出てしまい詰まっています。

サブクエリーで別名にしてるんですから、その名称で指定する必要があります。

SELECT 商品名,
  max(CASE WHEN 色= '赤' THEN 1 ELSE 0 END) AS 赤,

色をハードコーディングで指定してクロス集計しようという事ですから、colortableは不要だと思います。

SELEC 
, name AS 商品名
, sum(colorcode = '001') AS 赤
, sum(colorcode = '002') AS 青
, sum(colorcode = '003') AS 黄
, sum(colorcode = '004') AS 白
, sum(colorcode = '005') AS 黒
FROM  toys
group by name

Comments

[ooo_oooo]

ご回答ありがとうございます！
無事実装できました！
ありがとうございます！

Answer 2

idと商品名の関係がわかりません

create table tbl(id int primary key,goods varchar(10),color varchar(10),price int);
insert into tbl values
(1,'あああ','赤',100),
(2,'いいい','青',200),
(3,'ううう','白',300),
(4,'えええ','黄色',400),
(5,'あああ','赤',100),
(6,'あああ','青',100);

select goods ,
sum(color='赤') as 赤,
sum(color='青') as 青,
sum(color='白') as 白,
sum(color='黄色') as 黄色
from tbl
group by goods;

Comments

[ooo_oooo]

すみません、質問文を修正いたしました！