Убрать все группировки
select coalesce(a.school_name, b.school_name), a.req_volume, a.req_count, prod.prodazhi, prod.prodazhi_cnt, b.all_p as all_p, b.firstp, b.secondp, b.commision, b.all_p/plan.plan, (case when plan.second_plan > 0 then b.secondp/plan.second_plan else null end) as sobir_povtor, portfel.count_port, portfel.portfel, b.acquring
from (
select sum(deal.price) as req_volume, count(deal.id) as req_count, sch.school_name as school_name, sch.id as school_id
from deal_deal deal
left join (
select log.deal_id as id, min(log.dt) as dt
from deal_dealchangelog log group by log.deal_id ) startdate on startdate.id = deal.id
left join schools_school sch on deal.school_id = sch.id where deal.phone not in ('79997915643', '79295011053', '79163250530', '79279721312', '79250615541', '79268833082', '79057989233') and deal.school_id<>7 and coalesce(startdate.dt, '2022-01-01')::date > '2024-09-30' and coalesce(startdate.dt, '2022-01-01')::date < '2024-11-01' group by sch.school_name, sch.id ) a full join (
select sum(case when numb = 1 then amount else 0 end) as firstp, sum(case when numb <> 1 then amount else 0 end) as secondp, sum(amount) as all_p, sum(commision) as commision, sum(eq) as acquring, school_name
from (select date_paid as date, sum(fact.amount) as amount, sum(fact.amount*fact.margin) as commision, sum(case when payment_system=0 then amount else 0 end) * 0.0195 + sum(case when payment_system=1 then amount else 0 end) * 0.08 + sum(case when payment_system=2 then amount else 0 end) * 0.007 as eq, case when fact.id in (select fact1.id as id
from payment_calendar_paymentfact fact1
left join payment_calendar_paymentfact fact2 on fact1.deal_id=fact2.deal_id and fact1.date_paid>fact2.date_paid and fact1.operation='deposited' and fact1.status='1' and fact2.operation='deposited' and fact2.status='1' and fact1.deal_id is not null and fact2.deal_id is not null where fact1.operation='deposited' and fact1.status='1' and fact2.id is null) then 1 else 2 end as numb, sch.school_name
from payment_calendar_paymentfact fact
left join deal_deal deal on fact.deal_id=deal.id
left join schools_school sch on deal.school_id = sch.id where fact.operation='deposited' and fact.status='1' and deal.school_id<>7 group by date, numb, sch.school_name) a where date::date > '2024-09-30' and date::date < '2024-11-01' group by school_name ) b on a.school_name = b.school_name
left join (
select sum(case when pay_num <> 1 then plan else 0 end) as second_plan, sum(plan) as plan, school_name
from (select amount as plan, sch.school_name, plan.number as pay_num
from payment_calendar_paymentplan plan
left join deal_deal deal on plan.deal_id = deal.id
left join schools_school sch on deal.school_id = sch.id where plan_date::date > '2024-09-30' and plan_date::date < '2024-11-01' and deal.status >= 10 ) a group by school_name ) plan on plan.school_name = a.school_name or plan.school_name = b.school_name
left join(
select port_sum.sch_id, port_sum.school_name, coalesce(full_price , 0) - coalesce(paid, 0) as portfel, full_price, paid, count_port from(
select sch.id as sch_id, sch.school_name as school_name, count(distinct(deal.id)) as count_port, sum(deal.price) as full_price
from deal_deal deal
left join schools_school sch on deal.school_id = sch.id where deal_start_date::date < '2024-11-01' and deal.status >= 10 and deal.status not in (16,17) group by sch.id, school_name) port_sum
left join (
select sch.id as sch_id, school_name, sum(fact.amount) as paid
from payment_calendar_paymentfact fact
left join deal_deal deal on fact.deal_id = deal.id
left join schools_school sch on deal.school_id = sch.id where fact.date_paid < '2024-11-01' and fact.operation = 'deposited' and fact.status = '1' and deal.status not in (16,17) group by sch.id, school_name) port_paid on port_sum.sch_id = port_paid.sch_id order by portfel desc ) portfel on portfel.school_name = a.school_name or portfel.school_name = b.school_name
left join (
select sum(deal.price) as prodazhi, count(distinct(deal.id)) as prodazhi_cnt, sch.school_name as school_name
from deal_deal deal
left join schools_school sch on deal.school_id = sch.id where status>=10 and deal_start_date::date > '2024-09-30' and deal_start_date::date < '2024-11-01' group by sch.id, sch.school_name) prod on prod.school_name = a.school_name or prod.school_name = b.school_name order by case when commision is null then 1 else 0 end, commision desc