Task

-------------1
select reg.name,cit.name,indorg.name,org.small_name,obj.name,obj.building_volume,obj.heating_per_hour,sum(heatobj.gcal) from (select number, id_object,sum(gcal+gcal_double_glazing+gcal_loss_heat_valves_compensators_heating+gcal_loss_heat_valves_compensators_sewage) as gcal from com_jur_calc..calculation_heating where year = 2021
and number in (select number from com_jur_calc..contracts where year = 2021 and dead = 0 and id_office <> 37) group by number, id_object) heatobj
inner join (select * from com_jur_spr..objects where id not in (select id_object from com_jur_spr..ValuesTable where id_param = 30) and heating_per_hour <> 0 and building_volume >79) obj
on heatobj.id_object = obj.id

left join com_jur_calc..contracts ctr on heatobj.number = ctr.number
left join com_jur_spr..organizations org on ctr.id_organization = org.id
left join com_jur_spr..industry_organizations indorg on org.id_industry_organizations = indorg.id
left join com_jur_spr..regions reg on reg.id = obj.id_region
left join com_jur_spr..cities cit on cit.id = obj.id_city
group by reg.name,cit.name,indorg.name,org.small_name,obj.name,obj.building_volume,obj.heating_per_hour
having sum(heatobj.gcal) <> 0
order by reg.name,cit.name,indorg.name,org.small_name,obj.name,obj.building_volume,obj.heating_per_hour
--------------2
select otrasl,reg,cit,sum(kkalhour) as 'ккал/час всего' from
(
select reg.name as reg,cit.name as cit,indorg.name as otrasl,org.small_name,obj.name,obj.building_volume,obj.heating_per_hour as kkalhour,sum(heatobj.gcal) as gcalgod from (select number, id_object,sum(gcal+gcal_double_glazing+gcal_loss_heat_valves_compensators_heating+gcal_loss_heat_valves_compensators_sewage) as gcal from com_jur_calc..calculation_heating where year = 2021
and number in (select number from com_jur_calc..contracts where year = 2021 and dead = 0 and id_office <> 37) group by number, id_object) heatobj
inner join (select * from com_jur_spr..objects where id not in (select id_object from com_jur_spr..ValuesTable where id_param = 30) and heating_per_hour <> 0 and building_volume >79) obj
on heatobj.id_object = obj.id

left join com_jur_calc..contracts ctr on heatobj.number = ctr.number
left join com_jur_spr..organizations org on ctr.id_organization = org.id
left join com_jur_spr..industry_organizations indorg on org.id_industry_organizations = indorg.id
left join com_jur_spr..regions reg on reg.id = obj.id_region
left join com_jur_spr..cities cit on cit.id = obj.id_city
group by reg.name,cit.name,indorg.name,org.small_name,obj.name,obj.building_volume,obj.heating_per_hour
having sum(heatobj.gcal) <> 0
) tab
group by otrasl,reg,cit
order by reg,cit