Figure 7 EU

select CASE 
 WHEN s2.surfaceWaterBodyCategory = 'LW' THEN '2'
 WHEN s2.surfaceWaterBodyCategory = 'RW' THEN '1'
 WHEN s2.surfaceWaterBodyCategory = 'TW' THEN '3'
 WHEN s2.surfaceWaterBodyCategory = 'CW' THEN '4'
END as "filtersOrder"
, ROUND(CAST(UWWnumber as float)/CAST(Total as float)*100, 2) as "UWW"
, ROUND(CAST(UDnumber as float)/CAST(Total as float)*100, 2) as "UD"
, ROUND(CAST(SWOnumber as float)/CAST(Total as float)*100, 2) as "SWO" 
, CASE 
 WHEN s2.surfaceWaterBodyCategory = 'LW' THEN 'Lakes'
 WHEN s2.surfaceWaterBodyCategory = 'RW' THEN 'Rivers'
 WHEN s2.surfaceWaterBodyCategory = 'TW' THEN 'Transitional waters'
 WHEN s2.surfaceWaterBodyCategory = 'CW' THEN 'Coastal waters'
END as "Name", s2.countryCode as "country_code", UWWnumber, UDnumber, SWOnumber, Total
from (
SELECT surfaceWaterBodyCategory,'EU' as "country_code"
,COUNT(CASE WHEN swSignificantPressureType = 'P1-1 - Point - Urban waste water' THEN fileUrl ELSE null END) AS "UWWnumber"
,COUNT(CASE WHEN swSignificantPressureType = 'P2-6 - Diffuse - Discharges not connected to sewerage network' THEN fileUrl ELSE null END) AS "UDnumber"
,COUNT(CASE WHEN swSignificantPressureType = 'P1-2 - Point - Storm overflows' THEN fileUrl ELSE null END) AS "SWOnumber"
from WISE_WFD.latest.SWB_SurfaceWaterBody_swSignificantPressureType 
where ((countryCode not in ('BG', 'FI', 'HU', 'MT', 'SI') and cYear = '2022')
or (countryCode in ('BG', 'FI', 'HU', 'MT', 'SI') and cYear = '2016'))
and countryGroup = 'EU27'
-- and swSignificantPressureType in ('P1-1 - Point - Urban waste water', 'P2-6 - Diffuse - Discharges not connected to sewerage network', 'P1-2 - Point - Storm overflows')
and surfaceWaterBodyCategory in ('LW', 'RW', 'TW', 'CW') and countryGroup = 'EU27'
group by surfaceWaterBodyCategory) as s1
FULL OUTER JOIN (
    SELECT 'EU' as countryCode, surfaceWaterBodyCategory
    ,COUNT(distinct euSurfaceWaterBodyCode) as "Total"
    FROM WISE_WFD.v2r1.SWB_SurfaceWaterBody_swSignificantPressureType
    where ((countryCode not in ('BG', 'FI', 'HU', 'MT', 'SI') and cYear = '2022')
    or (countryCode in ('BG', 'FI', 'HU', 'MT', 'SI') and cYear = '2016'))
    and surfaceWaterBodyCategory is not null
    and countryGroup = 'EU27'
    GROUP BY surfaceWaterBodyCategory
) as s2
on s1.country_code = s2.countryCode and s1.surfaceWaterBodyCategory = s2.surfaceWaterBodyCategory
UNION
Select ISNULL(filtersOrder , '5') as "filtersOrder"
, ROUND(CAST(UWWnumber as float)/CAST(Total as float)*100, 2) as "UWW"
, ROUND(CAST(UDnumber as float)/CAST(Total as float)*100, 2) as "UD"
, ROUND(CAST(SWOnumber as float)/CAST(Total as float)*100, 2) as "SWO" 
, ISNULL(Name, 'Groundwaters') as "Name", g2.countryCode as "country_code", UWWnumber, UDnumber, SWOnumber, Total
from (
SELECT '5' as "filtersOrder",'Groundwaters' as "Name",'EU' as "country_code"
,SUM(distinct CASE WHEN gwSignificantPressureType = 'P1-1 - Point - Urban waste water' THEN cArea ELSE null END) AS "UWWnumber"
,SUM(distinct CASE WHEN gwSignificantPressureType = 'P2-6 - Diffuse - Discharges not connected to sewerage network' THEN cArea ELSE null END) AS "UDnumber"
,SUM(distinct CASE WHEN gwSignificantPressureType = 'P1-2 - Point - Storm overflows' THEN cArea ELSE null END) AS "SWOnumber"
from WISE_WFD.latest.GWB_GroundWaterBody_gwSignificantPressureType 
where ((countryCode not in ('BG', 'FI', 'HU', 'MT', 'SI') and cYear = '2022')
or (countryCode in ('BG', 'FI', 'HU', 'MT', 'SI') and cYear = '2016'))
and countryGroup = 'EU27'
-- and gwSignificantPressureType in ('P1-1 - Point - Urban waste water', 'P2-6 - Diffuse - Discharges not connected to sewerage network', 'P1-2 - Point - Storm overflows')
) as g1
FULL OUTER JOIN (
    SELECT
    'EU' as countryCode, 
    SUM(distinct cArea) as "Total"
    FROM WISE_WFD.v2r1.GWB_GroundWaterBody
    where ((countryCode not in ('BG', 'FI', 'HU', 'MT', 'SI') and cYear = '2022')
    or (countryCode in ('BG', 'FI', 'HU', 'MT', 'SI') and cYear = '2016'))
    and countryGroup = 'EU27'
) as g2
on g1.country_code = g2.countryCode
order by country_code, filtersOrder