Select Coalesce(pg.purchasemethodname, con.purchasemethodname, izv.purchasemethodname) As purchasemethodname,
       pg.rncpg, pg.summpg, con.rnccon, con.summcon, izv.rncizv, izv.summizv,' ' eco, ' ' eco_procent
From
(
Select Case When Upper(purchasemethodname) Like '%КОНКУРС%'  And purchasemethodcode  in('3359','200608') Then 'Конкурс определенный законом'
            When Upper(purchasemethodname) Like '%АУКЦИОН%'  And purchasemethodcode in ('3360','3361','200609') Then 'Аукцион определенный законом'
            When Upper(purchasemethodname) Like '%КОТИРОВ%' And purchasemethodcode in ('3362','200610') Then 'Запрос котировок'
            When Upper(purchasemethodname) Like '%ПРЕДЛОЖЕНИЙ%' And purchasemethodcode in ('200611') Then 'Запрос предложений'
            When Upper(purchasemethodname) Like '%ЕДИНСТВЕННОГО%'  And purchasemethodcode in ('3363') Then 'Закупка у единственного поставщика'
						   Else 'Иные конкурентные способы'
			   End As purchasemethodname, count(*) As rncpg, round(sum(maximumcontractprice::numeric)/1000,2) As summpg
From eis_purchaseplan_223_inn
Where shared='false' And
      status <> 'A' And
			bodyitempurchaseplandatareportingyear='2019' And
	    bodyitempurchaseplandatapublicationdatetime Between '2019-01-01'And '2019-12-31' And
			bodyitempurchaseplandatareportingyear='2019' And
			bodyitempurchaseplandatastartdate >='2019-01-01' And
			bodyitempurchaseplandataenddate<='2019-12-31'
Group by Case When Upper(purchasemethodname) Like '%КОНКУРС%'  And purchasemethodcode  in('3359','200608') Then 'Конкурс определенный законом'
            When Upper(purchasemethodname) Like '%АУКЦИОН%'  And purchasemethodcode in ('3360','3361','200609') Then 'Аукцион определенный законом'
            When Upper(purchasemethodname) Like '%КОТИРОВ%' And purchasemethodcode in ('3362','200610') Then 'Запрос котировок'
            When Upper(purchasemethodname) Like '%ПРЕДЛОЖЕНИЙ%' And purchasemethodcode in ('200611') Then 'Запрос предложений'
            When Upper(purchasemethodname) Like '%ЕДИНСТВЕННОГО%'  And purchasemethodcode in ('3363') Then 'Закупка у единственного поставщика'
						   Else 'Иные конкурентные способы'
			   End
) As pg Full Join
(
Select Case When Upper(bodyitemcontractdatapurchasetypeinfoname) Like '%КОНКУРС%' And bodyitemcontractdatapurchasetypeinfocode  in('11011') Then 'Конкурс определенный законом'
            When Upper(bodyitemcontractdatapurchasetypeinfoname) Like '%АУКЦИОН%'  And bodyitemcontractdatapurchasetypeinfocode in ('12012') Then 'Аукцион определенный законом'
            When Upper(bodyitemcontractdatapurchasetypeinfoname) Like '%ЕДИНСТВЕННОГО%' And bodyitemcontractdatapurchasetypeinfocode in ('30000') Then 'Закупка у единственного поставщика'
						   Else 'Иные конкурентные способы'
			   End
				 As purchasemethodname, count(*) As rnccon, round(sum(bodyitemcontractdataprice/1000)) As summcon
From eis_contracts_223_inn
Where to_timestamp(bodyitemcontractdatacontractdate) Between '2019-01-01'And '2019-12-31'
Group by Case When Upper(bodyitemcontractdatapurchasetypeinfoname) Like '%КОНКУРС%' And bodyitemcontractdatapurchasetypeinfocode  in('11011') Then 'Конкурс определенный законом'
            When Upper(bodyitemcontractdatapurchasetypeinfoname) Like '%АУКЦИОН%'  And bodyitemcontractdatapurchasetypeinfocode in ('12012') Then 'Аукцион определенный законом'
            When Upper(bodyitemcontractdatapurchasetypeinfoname) Like '%ЕДИНСТВЕННОГО%' And bodyitemcontractdatapurchasetypeinfocode in ('30000') Then 'Закупка у единственного поставщика'
						   Else 'Иные конкурентные способы'
			   End
) As con On (pg.purchasemethodname=con.purchasemethodname) Full Join
(
Select Case When Upper(bodyitempurchasenoticedatapurchasecodename) Like '%КОНКУРС%'  And bodyitempurchasenoticedatapurchasemethodcode  in('3359','200608') Then 'Конкурс определенный законом'
            When Upper(bodyitempurchasenoticedatapurchasecodename) Like '%АУКЦИОН%'  And bodyitempurchasenoticedatapurchasemethodcode in ('3360','3361','200609') Then 'Аукцион определенный законом'
            When Upper(bodyitempurchasenoticedatapurchasecodename) Like '%КОТИРОВ%' And bodyitempurchasenoticedatapurchasemethodcode in ('3362','200610') Then 'Запрос котировок'
            When Upper(bodyitempurchasenoticedatapurchasecodename) Like '%ПРЕДЛОЖЕНИЙ%' And bodyitempurchasenoticedatapurchasemethodcode in ('200611') Then 'Запрос предложений'
            When Upper(bodyitempurchasenoticedatapurchasecodename) Like '%ЕДИНСТВЕННОГО%'  And bodyitempurchasenoticedatapurchasemethodcode in ('3363') Then 'Закупка у единственного поставщика'
						   Else 'Иные конкурентные способы'
			   End As purchasemethodname, count(bodyitempurchasenoticedataregistrationnumber) As rncizv, round(sum(nmck::numeric/1000)) As summizv
From eis_notice_223all
Where nmck Not In ('',' ') And
      nmck is Not Null
Group by Case When Upper(bodyitempurchasenoticedatapurchasecodename) Like '%КОНКУРС%'  And bodyitempurchasenoticedatapurchasemethodcode  in('3359','200608') Then 'Конкурс определенный законом'
            When Upper(bodyitempurchasenoticedatapurchasecodename) Like '%АУКЦИОН%'  And bodyitempurchasenoticedatapurchasemethodcode in ('3360','3361','200609') Then 'Аукцион определенный законом'
            When Upper(bodyitempurchasenoticedatapurchasecodename) Like '%КОТИРОВ%' And bodyitempurchasenoticedatapurchasemethodcode in ('3362','200610') Then 'Запрос котировок'
            When Upper(bodyitempurchasenoticedatapurchasecodename) Like '%ПРЕДЛОЖЕНИЙ%' And bodyitempurchasenoticedatapurchasemethodcode in ('200611') Then 'Запрос предложений'
            When Upper(bodyitempurchasenoticedatapurchasecodename) Like '%ЕДИНСТВЕННОГО%'  And bodyitempurchasenoticedatapurchasemethodcode in ('3363') Then 'Закупка у единственного поставщика'
						   Else 'Иные конкурентные способы'
			   End
) As izv On (coalesce(pg.purchasemethodname,con.purchasemethodname)=izv.purchasemethodname)
Order by purchasemethodname