SQLserverで，合計を別のテーブルから呼び出して，表示させたい

Question

SQLserverの基礎的な練習を経たあと，
簡単な販売管理システムを作成しています。

そこで，
cust.csv
内の，id,name,mail,tel,amount （amountは顧客の支払い可能金額）
shouhin.csv
内の，id,cust,name,price

があり，顧客の各種データと，顧客ごとの商品の合計額と，顧客の支払い可能金額から商品の合計金額を差し引いたものを表示させたいです。
この合計の表示と差し引きの表示をSUMを使って行おうとしているのですが，
なぜか実現しません。

書いたコード
SELECT cust.id,cust.name,cust.mail,cust.tel,cust.amount,SUM(shouhin.amount),(amount - SUM(shouhin.amount)
FROM shouhin
JOIN cust ON shouhin.cust = cust.id
GROUP BY cust.id
ORDER BY cust.id;

SUMやgroupbyの使い方に問題があるのでしょうか。
なお，引き算の行を消した場合でも表示はなされません。

基礎的な質問で申し訳ないですが，宜しくお願いいたします。

Answer 1

A.Ichiさんのものとほぼ同じですが、外部結合ではcustに対応するshouhinレコードがない場合にSUMの結果の列がNULLとなるため、COALESCE関数を使用しています。

SELECT
	cust.id
	,cust.name
	,cust.mail
	,cust.tel
	,cust.amount
	,COALESCE(t1.goukei, 0) AS goukei
	,cust.amount - COALESCE(t1.goukei, 0) AS zangaku
FROM
	cust
	LEFT OUTER JOIN (
		SELECT
			cust
			,SUM(price) as goukei
		FROM
		 	shouhin
		GROUP BY
			cust 
	) as t1
		ON cust.id = t1.cust
ORDER BY
	cust.id
;

Comments

[currystyle]

ありがとうございます。
COALESCE関数をまだ理解していないので，勉強します。

Answer 2

MysqlでのSQLですが作成してみました。ご参考まで

SELECT cust.id,cust.name,
cust.mail,
cust.tel,
cust.amount,
t1.amount as kai, 
cust.amount - t1.amount as zan
FROM 
 (select cust, sum(amount) as amount from shouhin group by cust) as t1
JOIN cust ON t1.cust = cust.id
ORDER BY cust.id;

Comments

[currystyle]

ありがとうございます。
参考になりました。

Answer 3

SQLserverの基礎的な練習を経たあと，
簡単な販売管理システムを作成しています。

まだ、基礎を習得できていないレベルです。
提示されたSQLはエラーになると思われるんですが、結果が表示されていますか？

group by が指定されると、select句で選択できるのはgroup byで指定された項目とその項目に対して１行となる結果だけです。

考え方として、顧客ごとなので、cust.csvとshouhin.csvを顧客単位で集計したものを結びつけます。
データ量などで、性能が変わる場合があるので、２つの例を挙げておきます。

全体の一覧として

SELECT cust.*, shouhin.total
FROM cust left join (
    select cust , sum(price) as total from shouhin group by cust
) shouhin 
ON shouhin.cust = cust.id
ORDER BY cust.id

特定の顧客などの条件があるとき（whereの条件が無ければ結果は最初のものと同じ）

SELECT cust.*
     , (select sum(price) from shouhin where cust=cust.id) as total
FROM cust
where id=xxxx
ORDER BY id

Comments

[currystyle]

ありがとうございます。
できました。まだまだ基礎ができていなくて精進します。