---

select
    cast(o.ef_year as varchar(4)) as year_name,
    concat(ot.name, ' ', sh.number) as name,
    sum(o.value) as sum_value
from operations o
join back_office.operation_type ot
    on ot.id = o.operation_type_id
left join ourpension.sharer sh
    on o.owner_sharer_id = sh.id
where o.cred_mnemonics not like '%ИД%'
  and o.operation_type_id <> 106
  and ot.code not in (
      'DEPOSIT_ENROLLMENT',
      'PDS_DEPOSIT_ENROLLMENT',
      'PDS_RETURN_NDFL_REDEMPTION'
  )
group by o.ef_year, ot.name, sh.number
order by o.ef_year, ot.name, sh.number;