All official European Union website addresses are in the europa.eu domain.
See all EU institutions and bodiesSection 7 EU data entities part2
SELECT distinct 2022 as cYear, 'EU' as "country_code" -- ,ISNULL(TV9_1, 0) as TV9_1 -- ,ISNULL(TV9_2, 0) as TV9_2 -- ,ISNULL(TV9_3, 0) as TV9_3 ,ISNULL(TV9_4, 0) as TV9_4 ,ISNULL(TV9_5, 0) as TV9_5 -- ,ISNULL(TV9_6, 0) as TV9_6 -- ,ISNULL(TV9_7, 0) as TV9_7 -- ,ISNULL(TV9_8, 0) as TV9_8 ,ISNULL(TV9_9, 0) as TV9_9 ,ISNULL(TV9_10, 0) as TV9_10 -- ,ISNULL(TV9_11, 0) as TV9_11 -- ,ISNULL(TV9_12, 0) as TV9_12 -- ,ISNULL(TV9_13, 0) as TV9_13 ,ISNULL(TV9_14, 0) as TV9_14 ,ISNULL(TV9_15, 0) as TV9_15 from WISE_WFD.latest.SWB_SurfaceWaterBody_swSignificantPressureType as q1 -- RIGHT OUTER JOIN (select 'EU' as countryCode -- ,ROUND(CAST(COUNT(CASE WHEN swSignificantPressureType = 'P1-1 - Point - Urban waste water' THEN fileUrl ELSE null END) as float)/CAST(COUNT(distinct euSurfaceWaterBodyCode) as float)*100, 2) as 'TV9_1' -- ,ROUND(CAST(COUNT(CASE WHEN swSignificantPressureType = 'P2-6 - Diffuse - Discharges not connected to sewerage network' THEN fileUrl ELSE null END) as float)/CAST(COUNT(distinct euSurfaceWaterBodyCode) as float)*100, 2) as 'TV9_6' -- ,ROUND(CAST(COUNT(CASE WHEN swSignificantPressureType = 'P1-2 - Point - Storm overflows' THEN fileUrl ELSE null END) as float)/CAST(COUNT(distinct euSurfaceWaterBodyCode) as float)*100, 2) as 'TV9_11' -- from WISE_WFD.latest.SWB_SurfaceWaterBody_swSignificantPressureType as q2 -- 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 = 'RW' -- and countryGroup = 'EU27' -- ) RW -- on RW.countryCode = q1.countryCode -- RIGHT OUTER JOIN (select 'EU' as countryCode -- ,ROUND(CAST(COUNT(CASE WHEN swSignificantPressureType = 'P1-1 - Point - Urban waste water' THEN fileUrl ELSE null END) as float)/CAST(COUNT(distinct euSurfaceWaterBodyCode) as float)*100, 2) as 'TV9_2' -- ,ROUND(CAST(COUNT(CASE WHEN swSignificantPressureType = 'P2-6 - Diffuse - Discharges not connected to sewerage network' THEN fileUrl ELSE null END) as float)/CAST(COUNT(distinct euSurfaceWaterBodyCode) as float)*100, 2) as 'TV9_7' -- ,ROUND(CAST(COUNT(CASE WHEN swSignificantPressureType = 'P1-2 - Point - Storm overflows' THEN fileUrl ELSE null END) as float)/CAST(COUNT(distinct euSurfaceWaterBodyCode) as float)*100, 2) as 'TV9_12' -- from WISE_WFD.latest.SWB_SurfaceWaterBody_swSignificantPressureType as q2 -- 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 = 'LW' -- and countryGroup = 'EU27' -- ) LW -- on LW.countryCode = RW.countryCode -- RIGHT OUTER JOIN (select 'EU' as countryCode -- ,ROUND(CAST(COUNT(CASE WHEN swSignificantPressureType = 'P1-1 - Point - Urban waste water' THEN fileUrl ELSE null END) as float)/CAST(COUNT(distinct euSurfaceWaterBodyCode) as float)*100, 2) as 'TV9_3' -- ,ROUND(CAST(COUNT(CASE WHEN swSignificantPressureType = 'P2-6 - Diffuse - Discharges not connected to sewerage network' THEN fileUrl ELSE null END) as float)/CAST(COUNT(distinct euSurfaceWaterBodyCode) as float)*100, 2) as 'TV9_8' -- ,ROUND(CAST(COUNT(CASE WHEN swSignificantPressureType = 'P1-2 - Point - Storm overflows' THEN fileUrl ELSE null END) as float)/CAST(COUNT(distinct euSurfaceWaterBodyCode) as float)*100, 2) as 'TV9_13' -- from WISE_WFD.latest.SWB_SurfaceWaterBody_swSignificantPressureType as q2 -- 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 = 'TW' -- and countryGroup = 'EU27' -- ) TW -- on TW.countryCode = LW.countryCode RIGHT OUTER JOIN (select 'EU' as countryCode ,ROUND(CAST(COUNT(CASE WHEN swSignificantPressureType = 'P1-1 - Point - Urban waste water' THEN fileUrl ELSE null END) as float)/CAST(COUNT(distinct euSurfaceWaterBodyCode) as float)*100, 2) as 'TV9_4' ,ROUND(CAST(COUNT(CASE WHEN swSignificantPressureType = 'P2-6 - Diffuse - Discharges not connected to sewerage network' THEN fileUrl ELSE null END) as float)/CAST(COUNT(distinct euSurfaceWaterBodyCode) as float)*100, 2) as 'TV9_9' ,ROUND(CAST(COUNT(CASE WHEN swSignificantPressureType = 'P1-2 - Point - Storm overflows' THEN fileUrl ELSE null END) as float)/CAST(COUNT(distinct euSurfaceWaterBodyCode) as float)*100, 2) as 'TV9_14' from WISE_WFD.latest.SWB_SurfaceWaterBody_swSignificantPressureType as q2 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 = 'CW' and countryGroup = 'EU27' ) CW on CW.countryCode = q1.countryCode RIGHT OUTER JOIN (select 'EU' as countryCode ,ROUND(CAST(SUM(CASE WHEN gwSignificantPressureType = 'P1-1 - Point - Urban waste water' THEN cArea ELSE null END) as float)/CAST(SUM(distinct cArea) as float)*100, 2) as 'TV9_5' ,ROUND(CAST(SUM(CASE WHEN gwSignificantPressureType = 'P2-6 - Diffuse - Discharges not connected to sewerage network' THEN cArea ELSE null END) as float)/CAST(SUM(distinct cArea) as float)*100, 2) as 'TV9_10' ,ROUND(CAST(SUM(CASE WHEN gwSignificantPressureType = 'P1-2 - Point - Storm overflows' THEN cArea ELSE null END) as float)/CAST(SUM(distinct cArea) as float)*100, 2) as 'TV9_15' from WISE_WFD.latest.GWB_GroundWaterBody_gwSignificantPressureType as q2 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' ) GW on GW.countryCode = CW.countryCode
No data