SQL サブクエリについて

Question

現在、GCPにてSQLの練習をしております。

例えば、下記の★の部分にクエリ①を挿入したいときに、

select part, avg(a)
from high-territory-344913.practice220323.medical
group by part
having avg(a)>
(select avg(★)from high-territory-344913.practice220323.medical)

クエリ① (こちらは実行できることを確認しています)
select cast(substring(test, 2,3)as integer)
from high-territory-344913.practice220323.state

select part, avg(a)
from high-territory-344913.practice220323.medical
group by part
having avg(a)>
(select avg
(select cast(substring(test, 2,3)as integer)
from high-territory-344913.practice220323.state)
from high-territory-344913.practice220323.medical)
となると思うのですが、

Each function argument is an expression, not a query; to use a query as an expression, the query must be wrapped with additional parentheses to make it a scalar subquery expression at [6:10]
のエラーが出てしまいます。
(medicalテーブルのaの平均が、stateテーブルのtestの2,3,4文字目を数値化後の平均より大きい、
という条件を指定したい場合の例となります)

ここでお聞きしたいのですが、
上記エラーが出てしまっておりますが、構文が間違っているのでしょうか。解決法はありますでしょうか。

宜しくお願いいたします。

Comments

[hoshi-takanori]

★の部分にクエリ①を挿入する意味が分かりません。最終的にしたいことは何でしょうか？
〜
having avg(a)>
(select avg(cast(substring(test, 2,3)as integer))
from high-territory-344913.practice220323.state)
ってこと？

[chanoma]

ご連絡ありがとうございます。
stateテーブルのtestの2,3,4文字目を数値化後の平均より,medicalテーブルのaの方が大きい、
という条件を指定したいと思っています。あくまでサブクエリの練習の一環です。

またselectが重複していたことに気づきました。しかし下記を実行しようとしても、
Syntax error: Expected end of input but got keyword FROM at [10:1]のエラーが出てしまいました。

select part, avg(a)
from high-territory-344913.practice220323.medical
group by part
having avg(a)>
(select avg
(
cast(substring(test, 2,3)as integer))
from high-territory-344913.practice220323.state
)
from high-territory-344913.practice220323.medical)