SQLiteグループ集計の操作

#グループ集計の操作
Select句最後の「group_avg_height」としているカラムをgroup_name内の平均としたいのですが、このままだとgroup_nameとnameが掛け合わさった平均になってしまいます。
どうしたらいいのでしょうか？

SELECT
	group_name,
	U.name,
	avg(height),
	max(height),
	sum(case when height <= 170 then 1 else 0 end)as under170,
	avg(height)as group_avg_height
FROM
	countries as U
INNER JOIN(
SELECT
	id,
	country_id,
	height
FROM
	players
)as A
on U.id = A.country_id

GROUP by group_name,name
ORDER by group_name,avg(height)DESC
;

YT0014

2021/12/25 00:49

テーブル定義、サンプルレコード、現在の出力、希望の出力を、質問文を編集して、ご提示ください。

行動規範の内容に同意します

回答1件

group_nameのみの集計を行ったものとjoinすれば良いと思います。

追記

group_nameのみの集計を行うサンプルです

Window関数

SQL
1select group_name, name
2     , avg(height)
3     , max(height)
4     , sum(case when height <= 170 then 1 else 0 end) as under170
5     , group_avg_height
6from (
7    SELECT  U.group_name, U.name, A.height
8         ,  avg(height) over(partition by group_name) as group_avg_height
9    from    countries as U
10            inner join players as A
11            on U.id = A.country_id
12) UA
13GROUP by group_name, group_avg_height, name
14ORDER by group_name, group_avg_height DESC

相関問合せ

SQL
1with UA as (
2   select U.id, U.group_name, U.name, A.height
3   from   countries as U
4           inner join players as A
5           on U.id = A.country_id
6)
7select group_name, name
8    , avg(height),
9    , max(height),
10    , sum(case when height <= 170 then 1 else 0 end) as under170
11    , group_avg_height
12from (
13   SELECT *
14        , (select avg(height) from UA
15           where id=base.ID and group_name=base.group_name
16          ) as group_avg_height
17   FROM   UA as base
18) UA
19GROUP by group_name, group_avg_height, name
20ORDER by group_name, group_avg_height DESC