sql - 从 GROUP BY 中除以总和

Question

我的目标是将一个范围内的所有值相加，然后将该总和除以该值的总数。

通过下面的查询，我可以使用 a 对值求和GROUP BY：

select
sum(cast(json_data -> 'surface_extension' -> 'cold' -> 'percentage' as float)) as cold_perc,
sum(cast(json_data -> 'surface_extension' -> 'coolest' -> 'percentage' as float)) as coolest_perc,
sum(cast(json_data -> 'surface_extension' -> 'comfort' -> 'percentage' as float)) as comfort_perc,
sum(cast(json_data -> 'surface_extension' -> 'hot' -> 'percentage' as float)) as hot_perc,
sum(cast(json_data -> 'surface_extension' -> 'very_hot' -> 'percentage' as float)) as very_hot_perc,
area_urban_id
from project_urbaninfo
where area_urban_id = 3 and item_service_id = 29 and infotype_id = 1
group by area_urban_id 

通过下一个查询，我可以计算所有值：

select count(1)
from project_urbaninfo
where area_urban_id = 3 and item_service_id = 29 and infotype_id = 1

我的问题是如何加入这两个结果。

编辑：我的目标是获得这样的东西：

sum(cast(json_data -> 'surface_extension' -> 'cold' -> 'percentage' as float)) / COUNT as cold_perc

标签： sqlpostgresql

Answer 1

请试试这个。

select
sum(cast(json_data -> 'surface_extension' -> 'cold' -> 'percentage' as float))/COUNT(1) as cold_perc,
sum(cast(json_data -> 'surface_extension' -> 'coolest' -> 'percentage' as float))/COUNT(1) as coolest_perc,
sum(cast(json_data -> 'surface_extension' -> 'comfort' -> 'percentage' as float))/COUNT(1) as comfort_perc,
sum(cast(json_data -> 'surface_extension' -> 'hot' -> 'percentage' as float))/COUNT(1) as hot_perc,
sum(cast(json_data -> 'surface_extension' -> 'very_hot' -> 'percentage' as float))/COUNT(1) as very_hot_perc,
area_urban_id
from project_urbaninfo
where area_urban_id = 3 and item_service_id = 29 and infotype_id = 1
group by area_urban_id