--GDO eye incidence code 
 with 
 --AGE CTE 
 AGE_DATA as ( 
 select * from GDO.age)--STAGE CTE 
 , STAGE_DATA as ( 
 select * from GDO.stage),
tumour_cohort_eye
--Run on casref01

  tumour_cohort_eye as
(
select
att.tumourid 
, att.SITE_ICD10R4_O2_FROM2013
, att.diagnosisyear
, att.morph_icdo3rev2011
, att.age

from av2019.av_tumour_england att

where att.SITE_ICD10R4_O2_3CHAR_FROM2013 = 'C69'
and att.statusofregistration like 'F'
and att.ctry_code like 'E'
and att.dedup_flag=1 -- Excluding duplicates
and att.age between 0 and 200 -- Sensible age
and att.sex in (1,2) -- Known sex
and att.diagnosisyear between 2013 and 2019
)
 


,
statisticofinterest_eye as 
(select tumourid as tumourid_1, 1 as statisticofinterest_eye from tumour_cohort_eye),
tumour_cohort_linked_eye AS ( SELECT * FROM tumour_cohort_eye tc_eye
LEFT JOIN statisticofinterest_eye ON statisticofinterest_eye.tumourid_1 = tc_eye.tumourid
LEFT JOIN AGE_DATA age ON age.AGE = tc_eye.AGE
LEFT JOIN GDO.morph_eye morph ON morph.MORPH_ICDO3REV2011 = tc_eye.MORPH_ICDO3REV2011
LEFT JOIN ANALYSISCHARLOTTEEVERSFIELD.site_eye site ON site.SITE_ICD10R4_O2_FROM2013 = tc_eye.SITE_ICD10R4_O2_FROM2013)
 
 
 , extractpartition_eye as ( 

SELECT '2013' AS "Year", 
 'All' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2013'

UNION ALL

SELECT '2013' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2013' 
AND SITE = 'Cancers of the eyeball'

UNION ALL

SELECT '2013' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Conjunctival squamous cell carcinoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2013' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Conjunctival squamous cell carcinoma'

UNION ALL

SELECT '2013' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2013' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma'

UNION ALL

SELECT '2013' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Conjunctival melanoma' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2013' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Conjunctival melanoma'

UNION ALL

SELECT '2013' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2013' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma'

UNION ALL

SELECT '2013' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 00-49' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2013' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 00-49'

UNION ALL

SELECT '2013' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 50-59' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2013' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 50-59'

UNION ALL

SELECT '2013' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 60-69' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2013' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 60-69'

UNION ALL

SELECT '2013' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 70+' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2013' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 70+'

UNION ALL

SELECT '2013' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Retinoblastoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2013' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Retinoblastoma'

UNION ALL

SELECT '2013' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Unspecified and other' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2013' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Unspecified and other'

UNION ALL

SELECT '2013' AS "Year", 
 'Orbit and lacrimal gland and duct cancers' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2013' 
AND SITE = 'Orbit and lacrimal gland and duct cancers'

UNION ALL

SELECT '2013' AS "Year", 
 'Overlapping lesions of eye and adnexa and unspecified cancers' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2013' 
AND SITE = 'Overlapping lesions of eye and adnexa and unspecified cancers'

UNION ALL

SELECT '2014' AS "Year", 
 'All' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2014'

UNION ALL

SELECT '2014' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2014' 
AND SITE = 'Cancers of the eyeball'

UNION ALL

SELECT '2014' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Conjunctival squamous cell carcinoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2014' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Conjunctival squamous cell carcinoma'

UNION ALL

SELECT '2014' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2014' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma'

UNION ALL

SELECT '2014' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Conjunctival melanoma' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2014' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Conjunctival melanoma'

UNION ALL

SELECT '2014' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2014' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma'

UNION ALL

SELECT '2014' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 00-49' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2014' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 00-49'

UNION ALL

SELECT '2014' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 50-59' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2014' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 50-59'

UNION ALL

SELECT '2014' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 60-69' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2014' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 60-69'

UNION ALL

SELECT '2014' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 70+' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2014' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 70+'

UNION ALL

SELECT '2014' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Retinoblastoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2014' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Retinoblastoma'

UNION ALL

SELECT '2014' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Unspecified and other' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2014' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Unspecified and other'

UNION ALL

SELECT '2014' AS "Year", 
 'Orbit and lacrimal gland and duct cancers' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2014' 
AND SITE = 'Orbit and lacrimal gland and duct cancers'

UNION ALL

SELECT '2014' AS "Year", 
 'Overlapping lesions of eye and adnexa and unspecified cancers' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2014' 
AND SITE = 'Overlapping lesions of eye and adnexa and unspecified cancers'

UNION ALL

SELECT '2015' AS "Year", 
 'All' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2015'

UNION ALL

SELECT '2015' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2015' 
AND SITE = 'Cancers of the eyeball'

UNION ALL

SELECT '2015' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Conjunctival squamous cell carcinoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2015' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Conjunctival squamous cell carcinoma'

UNION ALL

SELECT '2015' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2015' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma'

UNION ALL

SELECT '2015' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Conjunctival melanoma' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2015' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Conjunctival melanoma'

UNION ALL

SELECT '2015' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2015' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma'

UNION ALL

SELECT '2015' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 00-49' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2015' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 00-49'

UNION ALL

SELECT '2015' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 50-59' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2015' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 50-59'

UNION ALL

SELECT '2015' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 60-69' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2015' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 60-69'

UNION ALL

SELECT '2015' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 70+' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2015' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 70+'

UNION ALL

SELECT '2015' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Retinoblastoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2015' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Retinoblastoma'

UNION ALL

SELECT '2015' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Unspecified and other' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2015' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Unspecified and other'

UNION ALL

SELECT '2015' AS "Year", 
 'Orbit and lacrimal gland and duct cancers' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2015' 
AND SITE = 'Orbit and lacrimal gland and duct cancers'

UNION ALL

SELECT '2015' AS "Year", 
 'Overlapping lesions of eye and adnexa and unspecified cancers' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2015' 
AND SITE = 'Overlapping lesions of eye and adnexa and unspecified cancers'

UNION ALL

SELECT '2016' AS "Year", 
 'All' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2016'

UNION ALL

SELECT '2016' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2016' 
AND SITE = 'Cancers of the eyeball'

UNION ALL

SELECT '2016' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Conjunctival squamous cell carcinoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2016' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Conjunctival squamous cell carcinoma'

UNION ALL

SELECT '2016' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2016' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma'

UNION ALL

SELECT '2016' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Conjunctival melanoma' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2016' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Conjunctival melanoma'

UNION ALL

SELECT '2016' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2016' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma'

UNION ALL

SELECT '2016' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 00-49' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2016' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 00-49'

UNION ALL

SELECT '2016' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 50-59' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2016' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 50-59'

UNION ALL

SELECT '2016' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 60-69' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2016' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 60-69'

UNION ALL

SELECT '2016' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 70+' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2016' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 70+'

UNION ALL

SELECT '2016' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Retinoblastoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2016' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Retinoblastoma'

UNION ALL

SELECT '2016' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Unspecified and other' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2016' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Unspecified and other'

UNION ALL

SELECT '2016' AS "Year", 
 'Orbit and lacrimal gland and duct cancers' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2016' 
AND SITE = 'Orbit and lacrimal gland and duct cancers'

UNION ALL

SELECT '2016' AS "Year", 
 'Overlapping lesions of eye and adnexa and unspecified cancers' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2016' 
AND SITE = 'Overlapping lesions of eye and adnexa and unspecified cancers'

UNION ALL

SELECT '2017' AS "Year", 
 'All' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2017'

UNION ALL

SELECT '2017' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2017' 
AND SITE = 'Cancers of the eyeball'

UNION ALL

SELECT '2017' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Conjunctival squamous cell carcinoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2017' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Conjunctival squamous cell carcinoma'

UNION ALL

SELECT '2017' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2017' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma'

UNION ALL

SELECT '2017' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Conjunctival melanoma' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2017' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Conjunctival melanoma'

UNION ALL

SELECT '2017' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2017' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma'

UNION ALL

SELECT '2017' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 00-49' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2017' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 00-49'

UNION ALL

SELECT '2017' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 50-59' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2017' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 50-59'

UNION ALL

SELECT '2017' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 60-69' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2017' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 60-69'

UNION ALL

SELECT '2017' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 70+' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2017' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 70+'

UNION ALL

SELECT '2017' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Retinoblastoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2017' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Retinoblastoma'

UNION ALL

SELECT '2017' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Unspecified and other' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2017' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Unspecified and other'

UNION ALL

SELECT '2017' AS "Year", 
 'Orbit and lacrimal gland and duct cancers' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2017' 
AND SITE = 'Orbit and lacrimal gland and duct cancers'

UNION ALL

SELECT '2017' AS "Year", 
 'Overlapping lesions of eye and adnexa and unspecified cancers' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2017' 
AND SITE = 'Overlapping lesions of eye and adnexa and unspecified cancers'

UNION ALL

SELECT '2018' AS "Year", 
 'All' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2018'

UNION ALL

SELECT '2018' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2018' 
AND SITE = 'Cancers of the eyeball'

UNION ALL

SELECT '2018' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Conjunctival squamous cell carcinoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2018' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Conjunctival squamous cell carcinoma'

UNION ALL

SELECT '2018' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2018' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma'

UNION ALL

SELECT '2018' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Conjunctival melanoma' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2018' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Conjunctival melanoma'

UNION ALL

SELECT '2018' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2018' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma'

UNION ALL

SELECT '2018' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 00-49' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2018' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 00-49'

UNION ALL

SELECT '2018' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 50-59' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2018' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 50-59'

UNION ALL

SELECT '2018' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 60-69' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2018' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 60-69'

UNION ALL

SELECT '2018' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 70+' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2018' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 70+'

UNION ALL

SELECT '2018' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Retinoblastoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2018' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Retinoblastoma'

UNION ALL

SELECT '2018' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Unspecified and other' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2018' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Unspecified and other'

UNION ALL

SELECT '2018' AS "Year", 
 'Orbit and lacrimal gland and duct cancers' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2018' 
AND SITE = 'Orbit and lacrimal gland and duct cancers'

UNION ALL

SELECT '2018' AS "Year", 
 'Overlapping lesions of eye and adnexa and unspecified cancers' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2018' 
AND SITE = 'Overlapping lesions of eye and adnexa and unspecified cancers'

UNION ALL

SELECT '2019' AS "Year", 
 'All' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2019'

UNION ALL

SELECT '2019' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2019' 
AND SITE = 'Cancers of the eyeball'

UNION ALL

SELECT '2019' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Conjunctival squamous cell carcinoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2019' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Conjunctival squamous cell carcinoma'

UNION ALL

SELECT '2019' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2019' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma'

UNION ALL

SELECT '2019' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Conjunctival melanoma' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2019' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Conjunctival melanoma'

UNION ALL

SELECT '2019' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2019' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma'

UNION ALL

SELECT '2019' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 00-49' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2019' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 00-49'

UNION ALL

SELECT '2019' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 50-59' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2019' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 50-59'

UNION ALL

SELECT '2019' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 60-69' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2019' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 60-69'

UNION ALL

SELECT '2019' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 70+' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2019' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 70+'

UNION ALL

SELECT '2019' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Retinoblastoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2019' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Retinoblastoma'

UNION ALL

SELECT '2019' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Unspecified and other' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2019' 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Unspecified and other'

UNION ALL

SELECT '2019' AS "Year", 
 'Orbit and lacrimal gland and duct cancers' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2019' 
AND SITE = 'Orbit and lacrimal gland and duct cancers'

UNION ALL

SELECT '2019' AS "Year", 
 'Overlapping lesions of eye and adnexa and unspecified cancers' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR = '2019' 
AND SITE = 'Overlapping lesions of eye and adnexa and unspecified cancers'

UNION ALL

SELECT '2013-2015' AS "Year", 
 'All' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015

UNION ALL

SELECT '2013-2015' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015 
AND SITE = 'Cancers of the eyeball'

UNION ALL

SELECT '2013-2015' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Conjunctival squamous cell carcinoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Conjunctival squamous cell carcinoma'

UNION ALL

SELECT '2013-2015' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma'

UNION ALL

SELECT '2013-2015' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Conjunctival melanoma' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Conjunctival melanoma'

UNION ALL

SELECT '2013-2015' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma'

UNION ALL

SELECT '2013-2015' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 00-49' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 00-49'

UNION ALL

SELECT '2013-2015' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 50-59' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 50-59'

UNION ALL

SELECT '2013-2015' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 60-69' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 60-69'

UNION ALL

SELECT '2013-2015' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 70+' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 70+'

UNION ALL

SELECT '2013-2015' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Retinoblastoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Retinoblastoma'

UNION ALL

SELECT '2013-2015' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Unspecified and other' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Unspecified and other'

UNION ALL

SELECT '2013-2015' AS "Year", 
 'Orbit and lacrimal gland and duct cancers' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015 
AND SITE = 'Orbit and lacrimal gland and duct cancers'

UNION ALL

SELECT '2013-2015' AS "Year", 
 'Overlapping lesions of eye and adnexa and unspecified cancers' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015 
AND SITE = 'Overlapping lesions of eye and adnexa and unspecified cancers'

UNION ALL

SELECT '2014-2016' AS "Year", 
 'All' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016

UNION ALL

SELECT '2014-2016' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016 
AND SITE = 'Cancers of the eyeball'

UNION ALL

SELECT '2014-2016' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Conjunctival squamous cell carcinoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Conjunctival squamous cell carcinoma'

UNION ALL

SELECT '2014-2016' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma'

UNION ALL

SELECT '2014-2016' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Conjunctival melanoma' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Conjunctival melanoma'

UNION ALL

SELECT '2014-2016' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma'

UNION ALL

SELECT '2014-2016' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 00-49' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 00-49'

UNION ALL

SELECT '2014-2016' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 50-59' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 50-59'

UNION ALL

SELECT '2014-2016' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 60-69' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 60-69'

UNION ALL

SELECT '2014-2016' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 70+' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 70+'

UNION ALL

SELECT '2014-2016' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Retinoblastoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Retinoblastoma'

UNION ALL

SELECT '2014-2016' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Unspecified and other' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Unspecified and other'

UNION ALL

SELECT '2014-2016' AS "Year", 
 'Orbit and lacrimal gland and duct cancers' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016 
AND SITE = 'Orbit and lacrimal gland and duct cancers'

UNION ALL

SELECT '2014-2016' AS "Year", 
 'Overlapping lesions of eye and adnexa and unspecified cancers' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016 
AND SITE = 'Overlapping lesions of eye and adnexa and unspecified cancers'

UNION ALL

SELECT '2015-2017' AS "Year", 
 'All' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017

UNION ALL

SELECT '2015-2017' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017 
AND SITE = 'Cancers of the eyeball'

UNION ALL

SELECT '2015-2017' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Conjunctival squamous cell carcinoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Conjunctival squamous cell carcinoma'

UNION ALL

SELECT '2015-2017' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma'

UNION ALL

SELECT '2015-2017' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Conjunctival melanoma' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Conjunctival melanoma'

UNION ALL

SELECT '2015-2017' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma'

UNION ALL

SELECT '2015-2017' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 00-49' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 00-49'

UNION ALL

SELECT '2015-2017' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 50-59' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 50-59'

UNION ALL

SELECT '2015-2017' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 60-69' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 60-69'

UNION ALL

SELECT '2015-2017' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 70+' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 70+'

UNION ALL

SELECT '2015-2017' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Retinoblastoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Retinoblastoma'

UNION ALL

SELECT '2015-2017' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Unspecified and other' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Unspecified and other'

UNION ALL

SELECT '2015-2017' AS "Year", 
 'Orbit and lacrimal gland and duct cancers' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017 
AND SITE = 'Orbit and lacrimal gland and duct cancers'

UNION ALL

SELECT '2015-2017' AS "Year", 
 'Overlapping lesions of eye and adnexa and unspecified cancers' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017 
AND SITE = 'Overlapping lesions of eye and adnexa and unspecified cancers'

UNION ALL

SELECT '2016-2018' AS "Year", 
 'All' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2016 AND 2018

UNION ALL

SELECT '2016-2018' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2016 AND 2018 
AND SITE = 'Cancers of the eyeball'

UNION ALL

SELECT '2016-2018' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Conjunctival squamous cell carcinoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2016 AND 2018 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Conjunctival squamous cell carcinoma'

UNION ALL

SELECT '2016-2018' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2016 AND 2018 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma'

UNION ALL

SELECT '2016-2018' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Conjunctival melanoma' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2016 AND 2018 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Conjunctival melanoma'

UNION ALL

SELECT '2016-2018' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2016 AND 2018 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma'

UNION ALL

SELECT '2016-2018' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 00-49' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2016 AND 2018 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 00-49'

UNION ALL

SELECT '2016-2018' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 50-59' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2016 AND 2018 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 50-59'

UNION ALL

SELECT '2016-2018' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 60-69' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2016 AND 2018 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 60-69'

UNION ALL

SELECT '2016-2018' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 70+' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2016 AND 2018 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 70+'

UNION ALL

SELECT '2016-2018' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Retinoblastoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2016 AND 2018 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Retinoblastoma'

UNION ALL

SELECT '2016-2018' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Unspecified and other' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2016 AND 2018 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Unspecified and other'

UNION ALL

SELECT '2016-2018' AS "Year", 
 'Orbit and lacrimal gland and duct cancers' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2016 AND 2018 
AND SITE = 'Orbit and lacrimal gland and duct cancers'

UNION ALL

SELECT '2016-2018' AS "Year", 
 'Overlapping lesions of eye and adnexa and unspecified cancers' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2016 AND 2018 
AND SITE = 'Overlapping lesions of eye and adnexa and unspecified cancers'

UNION ALL

SELECT '2017-2019' AS "Year", 
 'All' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2017 AND 2019

UNION ALL

SELECT '2017-2019' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2017 AND 2019 
AND SITE = 'Cancers of the eyeball'

UNION ALL

SELECT '2017-2019' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Conjunctival squamous cell carcinoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2017 AND 2019 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Conjunctival squamous cell carcinoma'

UNION ALL

SELECT '2017-2019' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2017 AND 2019 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma'

UNION ALL

SELECT '2017-2019' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Conjunctival melanoma' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2017 AND 2019 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Conjunctival melanoma'

UNION ALL

SELECT '2017-2019' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2017 AND 2019 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma'

UNION ALL

SELECT '2017-2019' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 00-49' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2017 AND 2019 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 00-49'

UNION ALL

SELECT '2017-2019' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 50-59' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2017 AND 2019 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 50-59'

UNION ALL

SELECT '2017-2019' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 60-69' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2017 AND 2019 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 60-69'

UNION ALL

SELECT '2017-2019' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Ocular melanoma' AS "Tumour Type 2", 
 'Uveal melanoma' AS "Tumour Type 3", 
 'Age 70+' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2017 AND 2019 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Ocular melanoma' 
AND OCULAR_SITE = 'Uveal melanoma' 
AND SPLIT_50_TO_70 = 'Age 70+'

UNION ALL

SELECT '2017-2019' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Retinoblastoma' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2017 AND 2019 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Retinoblastoma'

UNION ALL

SELECT '2017-2019' AS "Year", 
 'Cancers of the eyeball' AS "Tumour Type", 
 'Unspecified and other' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2017 AND 2019 
AND SITE = 'Cancers of the eyeball' 
AND MORPH_GROUP = 'Unspecified and other'

UNION ALL

SELECT '2017-2019' AS "Year", 
 'Orbit and lacrimal gland and duct cancers' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2017 AND 2019 
AND SITE = 'Orbit and lacrimal gland and duct cancers'

UNION ALL

SELECT '2017-2019' AS "Year", 
 'Overlapping lesions of eye and adnexa and unspecified cancers' AS "Tumour Type", 
 'All' AS "Tumour Type 2", 
 'All' AS "Tumour Type 3", 
 'All' AS "Age", 
SUM(statisticofinterest_eye) as statistic_of_interest
FROM tumour_cohort_linked_eye
WHERE DIAGNOSISYEAR BETWEEN 2017 AND 2019 
AND SITE = 'Overlapping lesions of eye and adnexa and unspecified cancers'
 ) ,

--Geography lookup
                             lsoatoregionlookup as (
                             select distinct lsoa11_code as lsoa11, 
                             case 
                             when nhs_region_2021_name in ('East of England','Midlands') then 'Midlands and East of England'
                             when nhs_region_2021_name in ('North West', 'North East and Yorkshire') then 'North of England'
                             when nhs_region_2021_name in ('South West','South East') then 'South of England'
                             else nhs_region_2021_name
                             end as region
                             from av2019.at_geography_england
                              where substr(lsoa11_code,1,1) = 'E' 
                             ),lsoatoalllookup as(
                             select distinct lsoa11_code as lsoa11, 'All' as region 
                             from av2019.at_geography_england
                              where substr(lsoa11_code,1,1) = 'E')
                             , geoglookup as (
                             select * from lsoatoregionlookup
                             union
                             select * from lsoatoalllookup)
--Sex lookup 
                  , sexlookup as ( 
                  select 1 as basesex, '1' as sex from dual 
                  union
                  select 2 as basesex, '2' as sex from dual 
                  union
                  select 1 as basesex, 'All' as sex from dual
                  union
                  select 2 as basesex, 'All' as sex from dual)
 --Year lookup
                               --Select single years
                               ,yearlookup as (
                               select n as baseyear
                               , to_char(n) as year
                               from 
                               (select rownum n from dual
                               --Last year 
                               connect by level <=2019
                               ) 
                               --First year
                               where n >=2013 
 UNION
                                        select n as baseyear
                                        ,'2013-2015' as year
                                        from
                                        (select rownum n from dual
                                        --Last year
                                        connect by level <=2015
                                        )
                                        --First year
                                        where n >=2013
UNION
                                        select n as baseyear
                                        ,'2014-2016' as year
                                        from
                                        (select rownum n from dual
                                        --Last year
                                        connect by level <=2016
                                        )
                                        --First year
                                        where n >=2014
UNION
                                        select n as baseyear
                                        ,'2015-2017' as year
                                        from
                                        (select rownum n from dual
                                        --Last year
                                        connect by level <=2017
                                        )
                                        --First year
                                        where n >=2015
UNION
                                        select n as baseyear
                                        ,'2016-2018' as year
                                        from
                                        (select rownum n from dual
                                        --Last year
                                        connect by level <=2018
                                        )
                                        --First year
                                        where n >=2016
UNION
                                        select n as baseyear
                                        ,'2017-2019' as year
                                        from
                                        (select rownum n from dual
                                        --Last year
                                        connect by level <=2019
                                        )
                                        --First year
                                        where n >=2017 )
 --Age lookup
                       , agelookup as (
                       select distinct floor(AGE/5)+1 as baseage, 'All' as age from AGE_DATA 
UNION
select distinct floor(AGE/5)+1 as baseage, FIVEYEARAGE as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_50_70 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, TENYEARAGE as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_30_TO_70 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_70 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_50 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_50_TO_80 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_50_TO_70 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_70_80 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_20_50_70 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_20_50_70_80 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_30_50_70 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_30_50_70_80 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_60_70 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_60_TO_80 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_50_70_80 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_30 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_30_50 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_30_TO_50 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_20_40 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_20_TO_40 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_25 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_25_50 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_40 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_35 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_40_TO_70 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_5_10_20_30_50_70 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_20_30_50_70 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_20_TO_90 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_60_70_75_80 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_80 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_70_75_80 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_13_25_40_60_80 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_25_40_60_80 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_25_40_60 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_25_40_TO_80 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_5_THEN_10 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_60 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_60_80 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_40_TO_80 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_40_TO_90 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_25_70 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_25_60_TO_80 as age from AGE_DATA)
 
--Populations table
                              , population_table as (
                              select g.region, s.sex, y.year, a.age, sum(popcount) as popcount 
                              from ons2019.populations_normalised pops 
                              left outer join geoglookup g on pops.lsoa11 = g.lsoa11 
                              left outer join sexlookup s on pops.sex = s.basesex
                              left outer join yearlookup y on pops.year = y.baseyear
                              left outer join agelookup a on pops.quinaryagegroupint = a.baseage
                              group by g.region, s.sex, y.year, a.age
                              ),
 
eye as(
SELECT
'Eye cancer' AS "Cancer Site",
"Year" AS "Year",
"Tumour Type" AS "Tumour Type",
"Tumour Type 2" AS "Tumour Type 2",
"Tumour Type 3" AS "Tumour Type 3",
'Not split by Basis of Diagnosis' AS "Basis of Diagnosis",
'All stages' AS "Stage",
'Not split by Stage Detail' AS "Stage Detail",
'Not split by Stage Detail 2' AS "Stage Detail 2",
case when "Age" = 'All' then 'All ages' else "Age" end AS "Age",
'All England' AS "Region",
'Persons' AS "Sex",
'Not split by grade' AS "Grade",
'Not split by nodal status' AS "Nodal Status",
statistic_of_interest as "Incidence",
                          pops.popcount  as "Population"
from extractpartition_eye ep
                                     left outer join population_table pops
                                     on ep."Year" = pops.year 
and ep."Age" = pops.age where pops.region ='All' and pops.sex='All' 
 --End of code for eye
 ) select * from eye