GROUP BYの使いどころを教えてください。

Question

###前提・実現したいこと
My SQLで都道府県別・商品別に、販売額の合計を一覧にして出したいのですが、うまくいきません。

###発生している問題・エラーメッセージ
県IDが同じ場合は顧客番号にかかわらず、販売合計金額を1つに纏めたいのですが、CustomerIDを結合したせいでGROUP BYがかからなくなっているようです。
（CustomerIDを消すと県IDが取れなくなってしまうので、今使用されているID,Name,テーブルはそのまま使用したいです。）

###ソースコード
My SQL

SELECT
A.PrefecturalID
,A.PrefecturalName
,I.ProductName
,G.販売合計金額
FROM
Prefecturals AS A
INNER JOIN
(
SELECT
b.PrefecturalID
,c.CustomerID
FROM
Prefecturals AS b
INNER JOIN
Customers AS c
ON b.PrefecturalID = c.PrefecturalID
GROUP BY
b.PrefecturalID
,c.CustomerID
) AS D
ON A.PrefecturalID = D.PrefecturalID
INNER JOIN
(
SELECT
f.CustomerID
,e.ProductID
,SUM(f.Quantity * e.Price) AS 販売合計金額
FROM
Products AS e
INNER JOIN
Sales AS f
ON e.ProductID = f.ProductID
GROUP BY
e.ProductID
,f.CustomerID
) AS G
ON D.CustomerID = G.CustomerID
INNER JOIN
Products AS I
ON
I.ProductID = G.ProductID
ORDER BY
A.PrefecturalID
,A.PrefecturalName
,I.ProductName
;

###補足情報

テーブル結合の仕方は他にもあるかと思いますが、今回はNameをGROUP BYしないようにコードを組みたいのでこのような形になっています。

出力項目：PrefecturalID、PrefecturalName、ProductName、販売合計金額
出力順：PrefecturalID、PrefecturalName、ProductName

Answer 1

CustomerIDを結合したせいでGROUP BYがかからなくなっている

最初シンタックスエラーが出ているのかと思いましたが、どうやら顧客別に集計されてしまうのが外せないといった内容のようですね。
最小限の修正ですますなら、一番外でもう一度Groupすれば良いと思います。

SELECT A.PrefecturalID, A.PrefecturalName, I.ProductName, SUM(G.p) AS 販売合計金額  
FROM Prefecturals AS A
INNER JOIN (
	SELECT b.PrefecturalID, c.CustomerID
	FROM Prefecturals AS b
	INNER JOIN Customers AS c ON b.PrefecturalID = c.PrefecturalID
	GROUP BY b.PrefecturalID, c.CustomerID
) AS D ON A.PrefecturalID = D.PrefecturalID
INNER JOIN (
	SELECT f.CustomerID, e.ProductID, SUM(f.Quantity * e.Price) AS p
	FROM  Products AS e
	INNER JOIN Sales AS f ON e.ProductID = f.ProductID
	GROUP BY e.ProductID, f.CustomerID
) AS G ON D.CustomerID = G.CustomerID
INNER JOIN Products AS I ON I.ProductID = G.ProductID
GROUP BY A.PrefecturalID, A.PrefecturalName, I.ProductName
ORDER BY A.PrefecturalID, A.PrefecturalName, I.ProductName

ところでこのSQLはこのままでも動作するとは思うのですが、相当変かもしれません。
DBの中身が分からないのでなんともいえませんが、ひょっとしてこれでも同じ結果が得られるのでは？

SELECT
	Base.PrefecturalID, PrefName.PrefecturalName
	, ProdName.ProductName, Base.SumP AS 販売合計金額
FROM (
	SELECT Pref.PrefecturalID, Prod.ProductID, SUM(Sale.Quantity * Prod.Price) AS SumP
	FROM Prefecturals Pref
	INNER JOIN Customers Cust ON Pref.PrefecturalID = Cust.PrefecturalID
	INNER JOIN Sales Sale ON Cust.CustomerID = Sale.CustomerID
	INNER JOIN Products Prod ON Sale.ProductID = Prod.ProductID
	GROUP BY Pref.PrefecturalID, Prod.ProductID
) Base
INNER JOIN Prefecturals PrefName ON Base.PrefecturalID = PrefName.PrefecturalID
INNER JOIN Products ProdName ON Base.ProductID = ProdName.ProductID
ORDER BY Base.PrefecturalID, ProdName.ProductName

Comments

[yyy]

ありがとうございます！

これでも同じ結果が得られるのでは…と書いていただいたコードですが、私もそう思います。
そして見た目にもわかりやすいと思うのです。

しかし、上司にnameをGROUP BYするなと指示されており…困っていたところだったのです。
(サーバーに負担がかかるとかなんとか…)

わかりやすいご回答ありがとうございました！

[hirohiro]

ありゃ、「これでも同じ～」は下側のSQLですが、nameはグループしてませんよ？
GROUP BY Pref.PrefecturalID, Prod.ProductID
これだけです。

[yyy]

すみません、ORDER BYの部分を見てしまっていました…。

試してみたところ、確かに同じ結果が得られました。
自分で作ったものはサブクエリを2つに分けていたのですが、それを1つに纏め、必要な要素をいっぺんに抽出するような感じでしょうか。
わざわざCustomerIDを別で抽出していたので、それに紐づいたPrefecturalIDがグループ化をするときに引っかかっていたのですね。

[yyy]

以下、自分が使っているエイリアス名等たさせていただいたコードで完成しました。
SELECT
Z.PrefecturalID
,Z.PrefecturalName
,Y.ProductName
,X.販売合計金額
FROM
(
SELECT
A.PrefecturalID
,D.ProductID
,SUM(D.Price*C.Quantity) AS 販売合計金額
FROM
Prefecturals AS A
INNER JOIN
Customers AS B
ON A.PrefecturalID = B.PrefecturalID
INNER JOIN
Sales AS C
ON B.CustomerID = C.CustomerID
INNER JOIN
Products AS D
ON C.ProductID = D.ProductID
GROUP BY
A.PrefecturalID
,D.ProductID
) AS X
INNER JOIN
Products AS Y
ON
X.ProductID = Y.ProductID
INNER JOIN
Prefecturals AS Z
ON
X.PrefecturalID = Z.PrefecturalID
ORDER BY
Z.PrefecturalID
,Z.PrefecturalName
,Y.ProductName
;

つまっていたことが理解できました。hirohiroさん、本当にありがとうございました。

[hirohiro]

解決したようでよかったです。
はじめに書かれていたSQLのサブクエリDは、県＆企業の組が一意ではない場合は必要です。しかし、各テーブルの機能から考えて、同じ県に同じ企業(ID)が複数あるとは考えられません。(名前が同じとか、子会社などはありえますがIDは異なっているはずです。)だから普通はCustomersテーブルは「PrefecturalID,CustomerID」の組がユニークになるように制約を設定しているはずです。
（これがされていないとデータが変になる可能性が出てくるので良くないです。）
ということで、もともとユニークな「PrefecturalID,CustomerID」の組をDistinctしようとしているサブクエリDは最初から不要だったということになります。
Salesにも同じ企業(ID)へのおなじ売り上げ(ID)が二重に記録されるはずも無く
Productsにも同じ製品(ID)が複数登録されているはずもない
ので、それぞれDistinctしようとするコードは不要な場合が多いです。