集計方法について教えて下さい。PHP＋MYSQL

質問

こんにちは、よろしくお願い致します。
データベースに下記のようなデータが入っています。

table_A
cd name
---------------
1000001 aaa
1000002 bbb
1000003 ccc
1000004 ddd
1000005 eee
1000006 fff
1000007 ggg
1000008 hhh
1000009 iii
1000010 jjj

table_B
cd type point
---------------
1000001 A 100
1000001 B 50
1000001 C 30
1000004 C 20
1000005 B 70
1000009 A 10
1000009 C 40

table_Aと、table_Bから、下記table_Cの内容

table_C
cd name count point_total
------------------------------
1000001 aaa 3 180
1000002 bbb 0 0
1000003 ccc 0 0
1000004 ddd 1 20
1000005 eee 1 70
1000006 fff 0 0
1000007 ggg 0 0
1000008 hhh 0 0
1000009 iii 2 50
1000010 jjj 0 0

を得るには、どのようなSQLを書けば良いのでしょうか？

table_Cのcount項目は、cdでマッチするtable_Bのtypeの種類をカウントしたもので
table_Cのpoint_total項目は、cdでマッチするtable_Bのpointを合計した数値になります。

分かりにくい質問ですみませんがアドバイス頂けると嬉しいです。
具体的にどのような方法で実現できるか、教えて頂けると有難いです。
よろしくお願いいたします。

回答No.1

bin-chan · ベストアンサー

手元に環境が無いので無試験ですが。

SQL文はこんな感じ
※インデントに全角スペースを用いてるのでコピペの際には注意

SELECT tblA.cd, tblA.name, IFNULL(vwB.cnt, 0) as count, IFNULL(vwB.pnt_ttl) as point_total
FROM tableA as tblA LEFT JOIN
(
　SELECT cd, count(cd) as cnt, sum(point) as pnt_ttl
　FROM tableB
　GROUP BY cd
) as vwB
ON tblA.cd = vwB.CD
ORDER BY tblA.cd
;

結果をtableCとして保存したいなら、先頭に create table tableC as を追加
CREATE TABLE tableC as
SELECT tblA.cd, tblA.name, IFNULL(vwB.cnt, 0) as count, IFNULL(vwB.pnt_ttl) as point_total
FROM tableA as tblA LEFT JOIN
(
　SELECT cd, count(cd) as cnt, sum(point) as pnt_ttl
　FROM tableB
　GROUP BY cd
) as vwB
ON tblA.cd = vwB.CD
ORDER BY tblA.cd
;

お礼

とても助かりました。
本当にありがとうございます！

いまいろいろと行き詰ってることがありまして
近々、また質問すると思います。
今後ともよろしくお願い致します。

回答No.2

bin-chan

×）IFNULL(vwB.pnt_ttl) as point_total
○）IFNULL(vwB.pnt_ttl, 0) as point_total