Figure 8 (2025) cYear=2022

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,countryCode 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 cYear = '2022'
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')
group by countryCode, surfaceWaterBodyCategory) as s1
FULL OUTER JOIN (
select q3.countryCode,q3.surfaceWaterBodyCategory, q4.Total
from (
select distinct q1.countryCode, q2.surfaceWaterBodyCategory
FROM WISE_WFD.v2r1.SWB_SurfaceWaterBody_swSignificantPressureType as q1
CROSS JOIN (select distinct surfaceWaterBodyCategory
from WISE_WFD.v2r1.SWB_SurfaceWaterBody_swSignificantPressureType) as q2
where q2.surfaceWaterBodyCategory is not null
) q3
FULL OUTER JOIN (
SELECT
countryCode, surfaceWaterBodyCategory, 
COUNT(distinct euSurfaceWaterBodyCode) AS "Total"
FROM WISE_WFD.v2r1.SWB_SurfaceWaterBody_swSignificantPressureType
WHERE cYear IN ('2022')
GROUP BY countryCode, surfaceWaterBodyCategory
) q4
on q3.countryCode = q4.countryCode and q3.surfaceWaterBodyCategory = q4.surfaceWaterBodyCategory
where q3.countryCode is not null
) 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",countryCode as "country_code"
,SUM(CASE WHEN gwSignificantPressureType = 'P1-1 - Point - Urban waste water' THEN cArea ELSE null END) AS "UWWnumber"
,SUM(CASE WHEN gwSignificantPressureType = 'P2-6 - Diffuse - Discharges not connected to sewerage network' THEN cArea ELSE null END) AS "UDnumber"
,SUM(CASE WHEN gwSignificantPressureType = 'P1-2 - Point - Storm overflows' THEN cArea ELSE null END) AS "SWOnumber"
from WISE_WFD.latest.GWB_GroundWaterBody_gwSignificantPressureType 
where cYear = '2022'
and gwSignificantPressureType in ('P1-1 - Point - Urban waste water', 'P2-6 - Diffuse - Discharges not connected to sewerage network', 'P1-2 - Point - Storm overflows')
group by countryCode) as g1
FULL OUTER JOIN (
    SELECT
    countryCode, 
    SUM(distinct cArea) AS "Total"
    FROM WISE_WFD.v2r1.GWB_GroundWaterBody
    WHERE cYear IN ('2022')
    GROUP BY countryCode
) as g2
on g1.country_code = g2.countryCode
order by country_code, filtersOrder