Make a division of a sum from GROUP BY-CodePudding

My goal is to sum all values in a range and divide this sum with the total number of this values.

With the query below I can sum the values using 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

With the next query I can count all values:

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

My problem is how to join this two results.

EDIT: My aim is to obtain something like this:

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

CodePudding user response：

Please try this.

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