view

CREATE OR ALTER VIEW STATISTIC_CONTRACT(
    PROPOSAL_ID,
    SORT_WORK_LIST,
    COUNT_SORT_WORK,
    DEPARTMENT_EXECUTE_LIST,
    COUNT_DEPARTMENT_EXECUTE)
AS
select d.PROPOSAL_ID,
list(distinct(sw.NAME_SORT_WORK), ', ') as SORT_WORK_LIST,
count(distinct(sw.NAME_SORT_WORK)) as COUNT_SORT_WORK,
list(distinct(dp.NAME_SHORT), ', ') as DEPARTMENT_EXECUTE,
count(distinct(c.DEPARTMENT_ID_EXECUTE)) as COUNT_DEPARTMENT_EXECUTE
from DEVICES d
join SORT_WORK sw on d.SORT_WORK_ID = sw.ID
join CONTRACT c on c.PROPOSAL_ID = d.PROPOSAL_ID and c.DEPARTMENT_ID_EXECUTE <> 13
join DEPARTMENT dp on dp.ID = c.DEPARTMENT_ID_EXECUTE
group by d.PROPOSAL_ID
;

select distinct SORT_WORK_LIST from STATISTIC_CONTRACT where SORT_WORK_LIST <> 'null'