---

select
    s.id as sharer_id,
    s.number as sharer_number,
    t.id as transaction_id,
    o.id as operation_id,
    cat.mnemonics as account_mnemonics,
    t.effective_date,
    t.value as transaction_value,
    count(*) as rows_after_join,
    sum(t.value) as value_after_join,
    string_agg(
        distinct cast(io.number as text),
        ', '
    ) as payment_order_numbers
from back_office.transaction t
join back_office.operation o
    on o.id = t.operation_id
join back_office.account ca
    on ca.id = t.credit_account_id
join back_office.account_type cat
    on cat.id = ca.account_type_id
join back_office.account_type_group catg
    on catg.id = cat.account_type_group_id
    and catg.mnemonics = 'Н'
join ourpension.sharer s
    on s.id = ca.owner_sharer_id
join ourpension.individual i
    on i.id = s.individual_id
left join back_office.incoming_order_operation ioo
    on ioo.operation_id = o.id
left join back_office.incoming_order io
    on io.id = ioo.incoming_order_id
where o.approved is not null
    and o.deleted is null
    and i.last_name = 'Суханов'
    and i.first_name = 'Сергей'
    and i.middle_name = 'Сергеевич'
    and t.effective_date >= cast('2016-01-21' as date)
    and t.effective_date < cast('2016-01-22' as date)
group by
    s.id,
    s.number,
    t.id,
    o.id,
    cat.mnemonics,
    t.effective_date,
    t.value
order by
    s.number,
    t.id;