商品別・月別販売金額とその推移を出したい

Question

###前提・実現したいこと
2007年06月～08月までの商品ごとの販売金額を抽出し、更に前月からの増減を↑・→・↓で表示させる表を作りたい。
以下、増減以外を求めた表です。

SELECT
		B.ProductID
		,D.ProductName
		,SUM(CASE WHEN B.年月 = '2007-06' THEN B.Price*B.Quantity ELSE 0 END) AS "6月販売金額"
		,SUM(CASE WHEN B.年月 = '2007-07' THEN B.Price*B.Quantity ELSE 0 END) AS "7月販売金額"
		,SUM(CASE WHEN B.年月 = '2007-08' THEN B.Price*B.Quantity ELSE 0 END) AS "8月販売金額"
	 FROM
      		(
      	 	 SELECT
				C.ProductID
				,A.Quantity
				,C.Price
				,SUBSTR(A.SaleDate, 1, 7) AS 年月
      	 	 FROM
      			Sales AS A
					INNER JOIN
				Products AS C
					ON A.ProductID = C.ProductID
	  	 	 WHERE
				A.SaleDate BETWEEN '2007-06-01' AND '2007-08-31'
	 	 	 GROUP BY
				C.ProductID
				,A.Quantity
				,C.Price
				,A.SaleDate
      		) AS B
				INNER JOIN
			Products AS D
				ON B.ProductID = D.ProductID
GROUP BY
	B.ProductID
ORDER BY
	B.ProductID
;

###発生している問題・エラーメッセージ
一番下のサブクエリが働いていないようなのですが、JOINの仕方が間違っているのでしょうか？

###ソースコード

SELECT
    P.ProductID
    ,P.ProductName
    ,E."6月販売金額"
    ,E."7月販売金額"
    ,CASE WHEN E."6月販売金額" > E."7月販売金額" THEN '↓'
          WHEN E."6月販売金額" = E."7月販売金額" THEN '→'
          ELSE '↑' END AS "対6月増減"
    ,E."8月販売金額"
    ,CASE WHEN E."7月販売金額" > E."8月販売金額" THEN '↓'
          WHEN E."7月販売金額" = E."8月販売金額" THEN '→'
          ELSE '↑' END AS "対7月増減"
 FROM
        (
         SELECT
            W.ProductID
            ,SUBSTR(Q.SaleDate, 1, 7) AS 年月
         FROM
            Sales AS Q
                INNER JOIN
            Products AS W
         WHERE
            Q.SaleDate BETWEEN '2007-06-01' AND '2007-08-31'
         ) AS B
                INNER JOIN
			Products AS P
                ON B.ProductID = P.ProductID
				INNER JOIN
        (
         SELECT
            D.ProductID
            ,SUM(CASE WHEN B.年月 = '2007-06' THEN D.Price*C.Quantity ELSE 0 END) AS "6月販売金額"
            ,SUM(CASE WHEN B.年月 = '2007-07' THEN D.Price*C.Quantity ELSE 0 END) AS "7月販売金額"
            ,SUM(CASE WHEN B.年月 = '2007-08' THEN D.Price*C.Quantity ELSE 0 END) AS "8月販売金額"
         FROM
            Sales AS C
                INNER JOIN
            Products AS D
                ON C.ProductID = D.ProductID
         GROUP BY
            D.ProductID
        ) AS E
                ON P.ProductID = E.ProductID 
ORDER BY
    P.ProductID
;

###補足情報(言語/FW/ツール等のバージョンなど)

出力項目：ProductID、ProductName、6月販売金額、7月販売金額、対6月増減、8月販売金額、対7月増減
出力順：ProductID

Answer 1

こんにちは。

またまたお邪魔します。
テーブルEのサブクエリ内で、B.年月は存在してないかと思います。

Comments

[yyy]

Eのサブクエリより先にBのサブクエリが働いているとは限らないということですね…。
どうしてもこの形にしたいわけではないのですが、これがきれいなように見えてしまって…。

[退会済みユーザー]

こんにちは。

Eのサブクエリ内のSUMの3箇所を以下のようにしてみてはいかかでしょう。

【変更前】
SUM(CASE WHEN B.年月 = '2007-06' THEN D.Price*C.Quantity ELSE 0 END) AS "6月販売金額"

【変更後】
SUM(CASE WHEN SUBSTR(C.SaleDate, 1, 7) = '2007-06' THEN D.Price*C.Quantity ELSE 0 END) AS "6月販売金額"

試してみてください。
よろしくお願いします。

[yyy]

【変更後】に書いていただいたもので試してみたところ、not foundのエラーが出なくなりました。先に年月を抽出して繋ぐ必要なんてなかったんですね…。変なところにこだわってしまっていたようです。
まだ必要な行が出ていなかったりするので、もう少し弄ってみます。
ありがとうございます。

[yyy]

度々すみません。以下のように直してみたのですが、これだと6月から8月に販売履歴のないものの行が抜け落ちてしまいます。(SaleDateがNULLということだと思います。)

SUM(CASE WHEN SUBSTR(C.SaleDate, 1, 7) = '2007-06' THEN D.Price*C.Quantity ELSE 0 END) AS "6月販売金額"の部分に

WHEN SUBSTR(C.SaleDate, 1, 7) IS NULL THEN 0 ELSE 0

を付け足してみたのですが、0表記だったものがすべてNULLになりました…。
これってどこでNULLを0で表示するという指示を出さないといけないのでしょうか？

SELECT
P.ProductID
,P.ProductName
,E."6月販売金額"
,E."7月販売金額"
,CASE WHEN E."6月販売金額" > E."7月販売金額" THEN '↓'
WHEN E."6月販売金額" = E."7月販売金額" THEN '→'
ELSE '↑' END AS "対6月増減"
,E."8月販売金額"
,CASE WHEN E."7月販売金額" > E."8月販売金額" THEN '↓'
WHEN E."7月販売金額" = E."8月販売金額" THEN '→'
ELSE '↑' END AS "対7月増減"
FROM
Products AS P
LEFT OUTER JOIN
(
SELECT
D.ProductID
,SUM(CASE WHEN SUBSTR(C.SaleDate, 1, 7) = '2007-06' THEN D.Price*C.Quantity ELSE 0 END) AS "6月販売金額"
,SUM(CASE WHEN SUBSTR(C.SaleDate, 1, 7) = '2007-07' THEN D.Price*C.Quantity ELSE 0 END) AS "7月販売金額"
,SUM(CASE WHEN SUBSTR(C.SaleDate, 1, 7) = '2007-08' THEN D.Price*C.Quantity ELSE 0 END) AS "8月販売金額"
FROM
Sales AS C
INNER JOIN
Products AS D
ON C.ProductID = D.ProductID
WHERE
C.SaleDate BETWEEN '2007-06-01' AND '2007-08-31'
GROUP BY
D.ProductID
) AS E
ON P.ProductID = E.ProductID
ORDER BY
P.ProductID
;

[退会済みユーザー]

こんにちは。

NULLを 0 にしたいのであれば以下のような形でどうでしょうか。

---------- ↓ ここから ↓ ----------
SELECT
P.ProductID
, P.ProductName
, IFNULL(E.`6月販売金額`,0)
, IFNULL(E.`7月販売金額`,0)
, CASE WHEN IFNULL(E.`6月販売金額`,0) > IFNULL(E.`7月販売金額`,0) THEN '↓'
WHEN IFNULL(E.`6月販売金額`,0) = IFNULL(E.`7月販売金額`,0) THEN '→'
ELSE '↑' END AS "対6月増減"
, IFNULL(E.`8月販売金額`,0)
, CASE WHEN IFNULL(E.`7月販売金額`,0) > IFNULL(E.`8月販売金額`,0) THEN '↓'
WHEN IFNULL(E.`7月販売金額`,0) = IFNULL(E.`8月販売金額`,0) THEN '→'
ELSE '↑' END AS "対7月増減"
FROM
Products AS P
LEFT OUTER JOIN
(
SELECT
D.ProductID
, SUM(CASE WHEN SUBSTR(C.SaleDate, 1, 7) = '2007-06' THEN D.Price*C.Quantity ELSE 0 END) AS "6月販売金額"
, SUM(CASE WHEN SUBSTR(C.SaleDate, 1, 7) = '2007-07' THEN D.Price*C.Quantity ELSE 0 END) AS "7月販売金額"
, SUM(CASE WHEN SUBSTR(C.SaleDate, 1, 7) = '2007-08' THEN D.Price*C.Quantity ELSE 0 END) AS "8月販売金額"
FROM
Sales AS C
INNER JOIN Products AS D
ON C.ProductID = D.ProductID
WHERE
C.SaleDate BETWEEN '2007-06-01' AND '2007-08-31'
GROUP BY
D.ProductID
) AS E
ON P.ProductID = E.ProductID
ORDER BY
P.ProductID;
--------- ↑ ここまで ↑ ----------

確認してみてください。

[yyy]

ありがとうございます。
試してみたのですが、エラーになってしまいました。

それを受けて、条件を入れる順番を変えてみたら、販売履歴がない商品が3つだったのにそのうちの1つだけがなぜか表示されるようになりました…。不思議でたまりません…。

SELECT
P.ProductID
,P.ProductName
,E."6月販売金額"
,E."7月販売金額"
,CASE WHEN E."6月販売金額" > E."7月販売金額" THEN '↓'
WHEN E."6月販売金額" = E."7月販売金額" THEN '→'
ELSE '↑' END AS "対6月増減"
,E."8月販売金額"
,CASE WHEN E."7月販売金額" > E."8月販売金額" THEN '↓'
WHEN E."7月販売金額" = E."8月販売金額" THEN '→'
ELSE '↑' END AS "対7月増減"
FROM
Products AS P
LEFT OUTER JOIN
(
SELECT
D.ProductID
,SUM(CASE WHEN C.Quantity IS NULL THEN 0
WHEN SUBSTR(C.SaleDate, 1, 7) = '2007-06' THEN D.Price*C.Quantity
ELSE 0 END
) AS "6月販売金額"
,SUM(CASE WHEN C.Quantity IS NULL THEN 0
WHEN SUBSTR(C.SaleDate, 1, 7) = '2007-07' THEN D.Price*C.Quantity
ELSE 0 END
) AS "7月販売金額"
,SUM(CASE WHEN C.Quantity IS NULL THEN 0
WHEN SUBSTR(C.SaleDate, 1, 7) = '2007-08' THEN D.Price*C.Quantity
ELSE 0 END
) AS "8月販売金額"
FROM
Sales AS C
INNER JOIN
Products AS D
ON C.ProductID = D.ProductID
GROUP BY
D.ProductID
) AS E
ON P.ProductID = E.ProductID
ORDER BY
P.ProductID
;

[退会済みユーザー]

こんにちは。

もしよろしければ、
エラーの内容を張って頂けますでしょうか。

宜しくお願い致します。

[yyy]

すみません、エラーというか欲しい値が出ないという意味でした。

・IFNULL(E.`6月販売金額`,0)がそのまま列名で出てくる
　→これはASで別名を付ければ大丈夫ですよね

・対6月増減、対7月増減がNULL(ブランク)状態になる

◎＊月販売金額のNULL表示を0に変換したい
→これはできていました！

言葉が足りな過ぎでした。何度も申し訳ないです…。

[退会済みユーザー]

こんにちは。

>・対6月増減、対7月増減がNULL(ブランク)状態になる

この現象が再現できません。
今、私が適当に作ったデータは以下です。

【Products】
ProductID,ProductName,Price
0001,プロダクト１,100
0002,プロダクト２,200
0003,プロダクト３,300

【Sales】
0001,10,2007-06-02
0001,15,2007-07-02
0002,10,2007-07-08
0002,30,2007-08-19
0001,5,2007-08-26
0002,50,2007-06-09
0001,2,2007-06-03

【SQL】
SELECT
P.ProductID
, P.ProductName
, IFNULL(E.`6月販売金額`,0) AS '6月販売金額'
, IFNULL(E.`7月販売金額`,0) AS '7月販売金額'
, CASE WHEN IFNULL(E.`6月販売金額`,0) > IFNULL(E.`7月販売金額`,0) THEN '↓'
WHEN IFNULL(E.`6月販売金額`,0) = IFNULL(E.`7月販売金額`,0) THEN '→'
ELSE '↑' END AS '対6月増減'
, IFNULL(E.`8月販売金額`,0) AS '8月販売金額'
, CASE WHEN IFNULL(E.`7月販売金額`,0) > IFNULL(E.`8月販売金額`,0) THEN '↓'
WHEN IFNULL(E.`7月販売金額`,0) = IFNULL(E.`8月販売金額`,0) THEN '→'
ELSE '↑' END AS '対7月増減'
FROM
Products AS P
LEFT OUTER JOIN
(
SELECT
D.ProductID
, SUM(CASE WHEN SUBSTR(C.SaleDate, 1, 7) = '2007-06' THEN D.Price*C.Quantity ELSE 0 END) AS '6月販売金額'
, SUM(CASE WHEN SUBSTR(C.SaleDate, 1, 7) = '2007-07' THEN D.Price*C.Quantity ELSE 0 END) AS '7月販売金額'
, SUM(CASE WHEN SUBSTR(C.SaleDate, 1, 7) = '2007-08' THEN D.Price*C.Quantity ELSE 0 END) AS '8月販売金額'
FROM
Sales AS C
INNER JOIN Products AS D
ON C.ProductID = D.ProductID
WHERE
C.SaleDate BETWEEN '2007-06-01' AND '2007-08-31'
GROUP BY
D.ProductID
) AS E
ON P.ProductID = E.ProductID
ORDER BY
P.ProductID;

【結果】
ProductID,ProductName,6月販売金額,7月販売金額,対6月増減,8月販売金額,対7月増減
0001,プロダクト１,1200,0,↓,500,↑
0002,プロダクト２,10000,2000,↓,6000,↑
0003,プロダクト３,0 ,0,→,0,→

この状況で何か認識が違うものはありますでしょうか。
ご指摘頂けたら幸いです。

よろしくお願い致します。

[yyy]

わざわざすみません。ありがとうございます。
結果として間違ったところはありません。その形が私が出したかったものです。
Shindo_NJSさんが書いてくださったコードをそのままコピペして使わせていただいたので、なぜこちらのみできないのか不明ですが…。環境等異なるのでしょうかね。

[退会済みユーザー]

おはようございます。

認識の違いがなく安心しました。
MySQLのバージョンを教えていただけますでしょうか。
当方、ver.5.5を使用しております。

[yyy]

大変申し訳ありませんが、学習用CD-ROMを使用しているためバージョン等わかりかねます。

[退会済みユーザー]

こんにちは。

一応、同じバージョンで確認しようと思っただけですのでお気になさらず。
バージョンで左右されるようなSQLでもない気がしますので。

現状の問題点としては、

>・対6月増減、対7月増減がNULL(ブランク)状態になる

だけでしょうか。

この箇所がNULLになる原因は比較式のいずれかがNULLである場合ですが、

>◎＊月販売金額のNULL表示を0に変換したい
>→これはできていました！

IFNULLは機能しているため、NULL対応も問題ないはずです。

CASE文を分解してみて比較式を確認してみてはいかがでしょうか。

【分解後】
,CASE WHEN IFNULL(E.`6月販売金額`,0) < IFNULL(E.`7月販売金額`,0) THEN '○' ELSE '×' END AS '対6月増減↑'
,CASE WHEN IFNULL(E.`6月販売金額`,0) > IFNULL(E.`7月販売金額`,0) THEN '○' ELSE '×' END AS '対6月増減↓'
,CASE WHEN IFNULL(E.`6月販売金額`,0) = IFNULL(E.`7月販売金額`,0) THEN '○' ELSE '×' END AS '対6月増減→'

原因は他にもあるかもしれませんが、
ひとつひとつ見つけていくしかなさそうですね。

お力になれず申し訳ございません。

よろしくお願い致します。

[yyy]

何故正しく表示されなかったのかわかりました！
どうやらサブクエリの中のJOIN

FROM
　　　Sales AS C
INNER JOIN
　　　 Products AS D
ON C.ProductID = D.ProductID

の部分が、Productｓテーブルを主にしたLEFT OUTER JOINでないとダメだったようです。(本当にしょうもないことでした…。)お時間を割いていただいてありがとうございました。

また、まだまだ勉強不足の為IFNULLの使い方がいまいちわからず、結局以下のような形にしました。

SELECT
P.ProductID
,P.ProductName
,E."6月販売金額"
,E."7月販売金額"
,CASE WHEN E."6月販売金額" > E."7月販売金額" THEN '↓'
WHEN E."6月販売金額" = E."7月販売金額" THEN '→'
ELSE '↑' END AS "対6月増減"
,E."8月販売金額"
,CASE WHEN E."7月販売金額" > E."8月販売金額" THEN '↓'
WHEN E."7月販売金額" = E."8月販売金額" THEN '→'
ELSE '↑' END AS "対7月増減"
FROM
Products AS P
INNER JOIN
(
SELECT
D.ProductID
,SUM(CASE WHEN C.Quantity IS NULL THEN 0
WHEN SUBSTR(C.SaleDate, 1, 7) = '2007-06' THEN D.Price*C.Quantity
ELSE 0 END
) AS "6月販売金額"
,SUM(CASE WHEN C.Quantity IS NULL THEN 0
WHEN SUBSTR(C.SaleDate, 1, 7) = '2007-07' THEN D.Price*C.Quantity
ELSE 0 END
) AS "7月販売金額"
,SUM(CASE WHEN C.Quantity IS NULL THEN 0
WHEN SUBSTR(C.SaleDate, 1, 7) = '2007-08' THEN D.Price*C.Quantity
ELSE 0 END
) AS "8月販売金額"
FROM
Products AS D
LEFT OUTER JOIN
Sales AS C
ON C.ProductID = D.ProductID
GROUP BY
D.ProductID
) AS E
ON P.ProductID = E.ProductID
ORDER BY
P.ProductID
;

お陰様で完成いたしました。色々とありがとうございました。

[退会済みユーザー]

こんにちは。

自己解決はスッキリしますよね。
おめでとうございます。

[date]

終わった後でいうのはなんですが、これyyyさんの最後の答えは普通に1064エラーになりませんか?
バージョンでならないというなら私の勉強不足ですが

[yyy]

自分のところで問題なく必要レコードが抽出されたので上記コードを載せています。
他のバージョンだとどこかおかしなところあるのでしょうか。

[退会済みユーザー]

こんにちは。

おそらくdateさんは、ここのソースをそのまま実行した可能性があります。

E."6月販売金額"のようなカラムの場合、
"(ダブルクォーテーション)でなく、
`(バッククォーテーション)となるはずですので、
変換すればエラーは解消されませんか？

お試しください。

[yyy]

皆さん仰っているようにシングルが正しいのでしょうけれど、
私のところでは数字の含まれるテーブル名の場合、ダブルにしないとエラーが返ってきてしまうのでそのままコードを載せてしまいました・・。

[date]

'(シングルクォーテーション)と
```ここに言語を入力
`(バッククォーテーション)
````は別です。
(バッククォーテーション)はテーブル名やカラム名などを囲むときに使用します。
AS '6月販売金額'は　エラーになりませんが、E."7月販売金額"の部分はエラーになるはずです。mysql5.6にて確認はしました。　
ダブルクォーテーションでエラーにならないなら、すいませんでした。