mySql で Group By して結果を取得する際に、Join 先に条件を指定したい。

Question

mysql の初心者です。どなたかご教授いただければ幸いです。
よろしくお願いいたします。

テーブルが下記の通り二つあります。

area_area テーブル

id      pref     explaine
1	TOKYO	 this is tokyo1
2	TOKYO	 this is tokyo2
3	KANAGAWA this is kanagawa1
4	KANAGAWA this is kanagawa2
5	CHIBA	 this is chiba1
6	CHIBA	 this is chiba2

area_city テーブル

id      name    popularit  area_id
1	AOYAMA	 1	      1
2	AKASAKA	1	      1
3	NIPPORI	0	      2
4	KURAMAE	0	      2
5	YOKOSUKA   1          3
6	KAWASAKI   0	      3
7	SAGAMI	 1	      4
8	YOKOHAMA   0	      4

以前までは下記のようなコードで「県」のレコードが何件あるかを取得していました。

SELECT COUNT(a.pref) FROM area_area AS a GROUP BY a.pref;
CHIBA	2
KANAGAWA	2
TOKYO	2

しかし今度は、紐づく area_city テーブルで popularity が最低でも一つある場合のみカウントしたいです。

id1の東京は AOYAMA AKASAKA と popularity が両方 1 なので count + 1
id2の東京は NIPPORI KURAMAE と popularity が両方 0 なので count + 0

id3の神奈川は YOKOSUKA は 0 で KAWASAKI が 1 なので count + 1
id4の神奈川は SAGAMI は 0 で YOKOHAMA が 1 なので count + 1

千葉はないので count 0

このことから下記のような結果が欲しいです。

CHIBA	0
KANAGAWA	2
TOKYO	1

すいませんが、どなたかご回答いただければ幸いです。
以上よろしくお願いいたします。

Answer 1

やりかたはいろいろあると思いますが、個人的にはスカラサブクエリと集約関数(SUM)を組み合わせる方法がよいかと思います。

SELECT 
  name, 
  SUM((SELECT LEAST(1, COUNT(*))
         FROM area_city AS B 
        WHERE A.id = B.area_id
          AND B.popularit = 1))
FROM area_area AS A 
GROUP BY name;

Comments

[sususu]

ありがとうございます。上記を実行したところ「Unknown column 'name' in 'field list'」と出てきてしまいました。
```
FROM area_city にし下記を実行いたしました。
SELECT
name,
SUM((SELECT LEAST(1, COUNT(*))
FROM area_city AS B
WHERE A.id = B.area_id
AND B.popularity = 1))
FROM area_city AS A
GROUP BY name;
```
すると、下記のような結果となりました。
```
AKASAKA 0
AOYAMA 1
KAWASAKI 0
KURAMAE 1
NIPPORI 1
SAGAMI 0
YOKOHAMA 0
YOKOSUKA 0
```
私の方が何か間違っていますでしょうか、ご教授いただければ幸いです。
よろしくお願いいたします。

[neko_the_shadow]

area_areaテーブルのカラムは(id, name, explaine)と(id, perf, explaine)のどちらが正しいですか?

(id, perf, explaine)が正しいのであればSQLは以下のようになります。

SELECT
perf,
SUM((SELECT LEAST(1, COUNT(*))
FROM area_city AS B
WHERE A.id = B.area_id
AND B.popularit = 1))
FROM area_area AS A
GROUP BY perf;

[sususu]

ありがとうございます。
ご提示いただいたコードで動きました！
焦って書き込んでしまい、カラム明を間違ってしまっておりました。
大変失礼いたしました、修正いたします。
ありがとうございました。

[sususu]

neko_the_shqdow 様

先日はご回答いただきどうもありがとうございました。
立て続けで申し訳ありません。
上記の結果CHIBA 0 の行を結果から除外したいのですが、どのように書けばよろしいでしょうか。
試行錯誤しているのですが解決できていない状況です。

再度質問させていただいているので、ご回答いただければ幸いです。
https://teratail.com/questions/343231?modal=q-comp
よろしくお願いいたします。