-- Tables required for this script are
-- ANALYSISCHARLIETURNER.stages
-- ANALYSISCHARLIETURNER.ages
-- ANALYSISCHARLIETURNER.testicular_morph


/*
Structure of this file.
1. Notes
2. Define tables:
 - tesicular_all table, which defines the cohort of testicular tumours
 - 'statisticofinterest' table which refers to the above, allows counting
 - 'pops_and_ages' population counts for years and ages, male only (brings in the age brackets from the ages table)
 - GDO_Testicular - This is the 'counting cases' table for testicular tumours by Year of diagnosis, Tumour Type, stage and age.
   This table contains some new rows: all rows with 2013-2015 and 2014-2016 were not in the original version from R and should be 
   checked to make sure they are the sum of the relevant years
 - TESTICULAR_POPS - This is the 'at-risk population' for the same cohorts as the testicular tumours, by Year and Age for England and males only
3. Join GDO_Testicular and TESTICULAR_POPS

This file contains groups for 2013, 2014, 2015, 2016, 2013-2015, 2014-2016.
*/

/*
NOTES FOR TESTICULAR_POPS

We can get from AGE to QUINARYAGEGROUPINT via
FLOOR(AGE/5,0)+1

Demographic splits for Testicular are:
YEAR: 2013, 2014, 2015, 2016, 2013-2015, 2014-2016
AGE: "All", split_30 and split_30_to_50
SEX: is all MALE
GEO: England only!
*/


--- define cohort for Testicular tumours for construction of GDO_Testicular table
WITH testicular_all as (select tumourid, diagnosisyear, morph_icd10_o2, stage_best, age
from av2016.av_tumour
-- C-code for testicular or D-code for testicular
-- (D-codes included after discussion with Brian Rous)
where (site_icd10_o2 like 'C62%'
or site_icd10_o2 like 'D29%')
-- standard filters from CASSOP#1
and statusofregistration like 'F'-- finalised cases only
and ctry_code like 'E' -- English cases only
and dedup_flag=1 -- Excluding duplicates
and age between 0 and 200 -- Sensible age
and sex = 1 -- Sex is 'correct' for this site
-- year filter
and diagnosisyear between 2013 and 2016),

--- used for GDO_Testicular table
statisticofinterest as
(select tumourid, 1 as statisticofinterest from TESTICULAR_ALL),

--- build the age groups into the populations table
--- this is for the testicular_pops table
pops_and_ages as (
select pops.year, pops.sex, ages.split_30_to_50, ages.split_30, sum(popcount) as popcount from 
ons2016.populations_normalised POPS
left join ages 
--- This is a slightly weird condition on the join, but it means we only get one row for age five year age group
on POPS.QUINARYAGEGROUPINT = floor(AGES.AGE/5)+1
where age/5 = floor(age/5)
AND YEAR BETWEEN 2013 AND 2016
and LSOA11 like 'E%'
and sex = 1 -- testicular is a male-only cancer
group by pops.year, pops.sex, ages.split_30_to_50, ages.split_30),

-- define the main counting table
GDO_TESTICULAR AS
(
/*
Removed this because we're not doing All Years any more, only three-year rolling.

SELECT 'All' AS "Year",
 'All' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2


UNION ALL
*/

SELECT '2013' AS "Year",
 'All' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2013'

UNION ALL

SELECT '2013' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2013' 
AND t_morph.TUMOUR_TYPE = 'Non-seminoma'

UNION ALL

SELECT '2013' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2013' 
AND t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1'

UNION ALL

SELECT '2013' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 00-29' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2013' 
AND t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30 = 'Age 00-29'

UNION ALL

SELECT '2013' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 30+' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2013' 
AND t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30 = 'Age 30+'

UNION ALL

SELECT '2013' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 2-3' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2013' 
AND t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 2-3'

UNION ALL

SELECT '2013' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage unknown' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2013' 
AND t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage unknown'

UNION ALL

SELECT '2013' AS "Year",
 'Other' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2013' 
AND t_morph.TUMOUR_TYPE = 'Other'

UNION ALL

SELECT '2013' AS "Year",
 'Seminoma' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2013' 
AND t_morph.TUMOUR_TYPE = 'Seminoma'

UNION ALL

SELECT '2013' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2013' 
AND t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1'

UNION ALL

SELECT '2013' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 00-29' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2013' 
AND t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30_TO_50 = 'Age 00-29'

UNION ALL

SELECT '2013' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 30-39' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2013' 
AND t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30_TO_50 = 'Age 30-39'

UNION ALL

SELECT '2013' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 40-49' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2013' 
AND t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30_TO_50 = 'Age 40-49'

UNION ALL

SELECT '2013' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 50+' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2013' 
AND t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30_TO_50 = 'Age 50+'

UNION ALL

SELECT '2013' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 2-3' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2013' 
AND t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 2-3'

UNION ALL

SELECT '2013' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage unknown' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2013' 
AND t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage unknown'

UNION ALL

SELECT '2014' AS "Year",
 'All' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2014'

UNION ALL

SELECT '2014' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2014' 
AND t_morph.TUMOUR_TYPE = 'Non-seminoma'

UNION ALL

SELECT '2014' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2014' 
AND t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1'

UNION ALL

SELECT '2014' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 00-29' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2014' 
AND t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30 = 'Age 00-29'

UNION ALL

SELECT '2014' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 30+' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2014' 
AND t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30 = 'Age 30+'

UNION ALL

SELECT '2014' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 2-3' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2014' 
AND t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 2-3'

UNION ALL

SELECT '2014' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage unknown' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2014' 
AND t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage unknown'

UNION ALL

SELECT '2014' AS "Year",
 'Other' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2014' 
AND t_morph.TUMOUR_TYPE = 'Other'

UNION ALL

SELECT '2014' AS "Year",
 'Seminoma' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2014' 
AND t_morph.TUMOUR_TYPE = 'Seminoma'

UNION ALL

SELECT '2014' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2014' 
AND t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1'

UNION ALL

SELECT '2014' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 00-29' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2014' 
AND t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30_TO_50 = 'Age 00-29'

UNION ALL

SELECT '2014' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 30-39' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2014' 
AND t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30_TO_50 = 'Age 30-39'

UNION ALL

SELECT '2014' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 40-49' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2014' 
AND t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30_TO_50 = 'Age 40-49'

UNION ALL

SELECT '2014' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 50+' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2014' 
AND t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30_TO_50 = 'Age 50+'

UNION ALL

SELECT '2014' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 2-3' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2014' 
AND t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 2-3'

UNION ALL

SELECT '2014' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage unknown' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2014' 
AND t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage unknown'

UNION ALL

SELECT '2015' AS "Year",
 'All' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2015'

UNION ALL

SELECT '2015' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2015' 
AND t_morph.TUMOUR_TYPE = 'Non-seminoma'

UNION ALL

SELECT '2015' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2015' 
AND t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1'

UNION ALL

SELECT '2015' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 00-29' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2015' 
AND t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30 = 'Age 00-29'

UNION ALL

SELECT '2015' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 30+' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2015' 
AND t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30 = 'Age 30+'

UNION ALL

SELECT '2015' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 2-3' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2015' 
AND t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 2-3'

UNION ALL

SELECT '2015' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage unknown' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2015' 
AND t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage unknown'

UNION ALL

SELECT '2015' AS "Year",
 'Other' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2015' 
AND t_morph.TUMOUR_TYPE = 'Other'

UNION ALL

SELECT '2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2015' 
AND t_morph.TUMOUR_TYPE = 'Seminoma'

UNION ALL

SELECT '2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2015' 
AND t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1'

UNION ALL

SELECT '2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 00-29' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2015' 
AND t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30_TO_50 = 'Age 00-29'

UNION ALL

SELECT '2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 30-39' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2015' 
AND t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30_TO_50 = 'Age 30-39'

UNION ALL

SELECT '2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 40-49' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2015' 
AND t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30_TO_50 = 'Age 40-49'

UNION ALL

SELECT '2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 50+' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2015' 
AND t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30_TO_50 = 'Age 50+'

UNION ALL

SELECT '2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 2-3' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2015' 
AND t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 2-3'

UNION ALL

SELECT '2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage unknown' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2015' 
AND t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage unknown'

UNION ALL

SELECT '2016' AS "Year",
 'All' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2016'

UNION ALL

SELECT '2016' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2016' 
AND t_morph.TUMOUR_TYPE = 'Non-seminoma'

UNION ALL

SELECT '2016' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2016' 
AND t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1'

UNION ALL

SELECT '2016' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 00-29' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2016' 
AND t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30 = 'Age 00-29'

UNION ALL

SELECT '2016' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 30+' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2016' 
AND t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30 = 'Age 30+'

UNION ALL

SELECT '2016' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 2-3' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2016' 
AND t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 2-3'

UNION ALL

SELECT '2016' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage unknown' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2016' 
AND t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage unknown'

UNION ALL

SELECT '2016' AS "Year",
 'Other' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2016' 
AND t_morph.TUMOUR_TYPE = 'Other'

UNION ALL

SELECT '2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2016' 
AND t_morph.TUMOUR_TYPE = 'Seminoma'

UNION ALL

SELECT '2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2016' 
AND t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1'

UNION ALL

SELECT '2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 00-29' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2016' 
AND t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30_TO_50 = 'Age 00-29'

UNION ALL

SELECT '2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 30-39' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2016' 
AND t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30_TO_50 = 'Age 30-39'

UNION ALL

SELECT '2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 40-49' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2016' 
AND t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30_TO_50 = 'Age 40-49'

UNION ALL

SELECT '2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 50+' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2016' 
AND t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30_TO_50 = 'Age 50+'

UNION ALL

SELECT '2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 2-3' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2016' 
AND t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 2-3'

UNION ALL

SELECT '2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage unknown' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR = '2016' 
AND t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage unknown'

UNION ALL

SELECT '2013-2015' AS "Year",
 'All' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR BETWEEN 2014 and 2016

UNION ALL

SELECT '2013-2015' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Non-seminoma'
AND DIAGNOSISYEAR BETWEEN 2013 and 2015

UNION ALL

SELECT '2013-2015' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1'
AND DIAGNOSISYEAR BETWEEN 2013 and 2015

UNION ALL

SELECT '2013-2015' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 00-29' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30 = 'Age 00-29'
AND DIAGNOSISYEAR BETWEEN 2013 and 2015

UNION ALL

SELECT '2013-2015' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 30+' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30 = 'Age 30+'
AND DIAGNOSISYEAR BETWEEN 2013 and 2015

UNION ALL

SELECT '2013-2015' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 2-3' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 2-3'
AND DIAGNOSISYEAR BETWEEN 2013 and 2015

UNION ALL

SELECT '2013-2015' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage unknown' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage unknown'
AND DIAGNOSISYEAR BETWEEN 2013 and 2015

UNION ALL

SELECT '2013-2015' AS "Year",
 'Other' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Other'
AND DIAGNOSISYEAR BETWEEN 2013 and 2015

UNION ALL

SELECT '2013-2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Seminoma'
AND DIAGNOSISYEAR BETWEEN 2013 and 2015

UNION ALL

SELECT '2013-2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1'
AND DIAGNOSISYEAR BETWEEN 2013 and 2015

UNION ALL

SELECT '2013-2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 00-29' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30_TO_50 = 'Age 00-29'
AND DIAGNOSISYEAR BETWEEN 2013 and 2015

UNION ALL

SELECT '2013-2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 30-39' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30_TO_50 = 'Age 30-39'
AND DIAGNOSISYEAR BETWEEN 2013 and 2015

UNION ALL

SELECT '2013-2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 40-49' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30_TO_50 = 'Age 40-49'
AND DIAGNOSISYEAR BETWEEN 2013 and 2015

UNION ALL

SELECT '2013-2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 50+' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30_TO_50 = 'Age 50+'
AND DIAGNOSISYEAR BETWEEN 2013 and 2015

UNION ALL

SELECT '2013-2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 2-3' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Seminoma'
AND stages.STAGE_SPLIT1_TEST = 'Stage 2-3'
AND DIAGNOSISYEAR BETWEEN 2013 and 2015

UNION ALL

SELECT '2013-2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage unknown' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage unknown'
AND DIAGNOSISYEAR BETWEEN 2013 and 2015
UNION ALL

---- 2014-2016 SECTION

SELECT '2014-2016' AS "Year",
 'All' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE DIAGNOSISYEAR BETWEEN 2014 and 2016

UNION ALL

SELECT '2014-2016' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Non-seminoma'
AND DIAGNOSISYEAR BETWEEN 2013 and 2015

UNION ALL

SELECT '2014-2016' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1'
AND DIAGNOSISYEAR BETWEEN 2014 and 2016

UNION ALL

SELECT '2014-2016' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 00-29' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30 = 'Age 00-29'
AND DIAGNOSISYEAR BETWEEN 2014 and 2016

UNION ALL

SELECT '2014-2016' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 30+' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30 = 'Age 30+'
AND DIAGNOSISYEAR BETWEEN 2014 and 2016

UNION ALL

SELECT '2014-2016' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 2-3' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 2-3'
AND DIAGNOSISYEAR BETWEEN 2014 and 2016

UNION ALL

SELECT '2014-2016' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage unknown' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Non-seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage unknown'
AND DIAGNOSISYEAR BETWEEN 2014 and 2016

UNION ALL

SELECT '2014-2016' AS "Year",
 'Other' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Other'
AND DIAGNOSISYEAR BETWEEN 2014 and 2016

UNION ALL

SELECT '2014-2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Seminoma'
AND DIAGNOSISYEAR BETWEEN 2014 and 2016

UNION ALL

SELECT '2014-2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1'
AND DIAGNOSISYEAR BETWEEN 2014 and 2016

UNION ALL

SELECT '2014-2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 00-29' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30_TO_50 = 'Age 00-29'
AND DIAGNOSISYEAR BETWEEN 2014 and 2016

UNION ALL

SELECT '2014-2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 30-39' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30_TO_50 = 'Age 30-39'
AND DIAGNOSISYEAR BETWEEN 2014 and 2016

UNION ALL

SELECT '2014-2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 40-49' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30_TO_50 = 'Age 40-49'
AND DIAGNOSISYEAR BETWEEN 2014 and 2016

UNION ALL

SELECT '2014-2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 50+' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 1' 
AND ages.SPLIT_30_TO_50 = 'Age 50+'
AND DIAGNOSISYEAR BETWEEN 2014 and 2016

UNION ALL

SELECT '2014-2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 2-3' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage 2-3'
AND DIAGNOSISYEAR BETWEEN 2014 and 2016

UNION ALL

SELECT '2014-2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage unknown' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM TESTICULAR_ALL T_ALL
LEFT JOIN statisticofinterest si ON si.tumourid = T_ALL.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = T_ALL.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = T_ALL.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = T_ALL.MORPH_ICD10_O2
WHERE t_morph.TUMOUR_TYPE = 'Seminoma' 
AND stages.STAGE_SPLIT1_TEST = 'Stage unknown'
AND DIAGNOSISYEAR BETWEEN 2014 and 2016),

--- define the population-counting table
TESTICULAR_POPS AS (
Select Year, Sex, COALESCE(AGE,'All Ages') as Age, population 
from (
SELECT  cast (year as varchar(9)) as year, 'Male' as SEX, SPLIT_30_TO_50 as Age, sum(popcount) as population
from pops_and_ages
where
sex = 1
group by cube (year, split_30_TO_50)

UNION ALL

-- now just get the 30 + group from the split_30
SELECT cast (year as varchar(4)) as year, 'Male' as SEX, 'Age 30+' as Age, sum(popcount) as population
from pops_and_ages
where
sex = 1
and split_30 like 'Age 30+'
group by cube (year)

UNION ALL

--- do all the same for 2013-2015

SELECT '2013-2015' as YEAR, 'Male' as SEX, SPLIT_30_TO_50 as Age, sum(popcount) as population
from pops_and_ages
where
sex = 1
and year between 2013 and 2015
group by cube (split_30_TO_50)

UNION ALL

-- now get the 30 + group from the split_30 for these years
SELECT '2013-2015' as YEAR, 'Male' as SEX, 'Age 30+' as Age, sum(popcount) as population
from pops_and_ages
where
sex = 1
and year between 2013 and 2015
and split_30 like 'Age 30+'

UNION ALL
--- do all the same for 2014-2016

SELECT '2014-2016' as YEAR, 'Male' as SEX, SPLIT_30_TO_50 as Age, sum(popcount) as population
from pops_and_ages
where
sex = 1
and year between 2014 and 2016
group by cube (split_30_TO_50)

UNION ALL

-- now get the 30 + group from the split_30 for these years
SELECT '2014-2016' as YEAR, 'Male' as SEX, 'Age 30+' as Age, sum(popcount) as population
from pops_and_ages
where
sex = 1
and year between 2014 and 2016
and split_30 like 'Age 30+'

)
--- get rid of the "all years" 2013-2016, because this is not consistent with other things.
where year is not null)

SELECT GDO_TESTICULAR.*, TESTICULAR_POPS.Population --- USE THE EXCEL SHEET FOR THE REST ON THIS OCCASION.
from GDO_TESTICULAR
LEFT OUTER JOIN TESTICULAR_POPS
on GDO_TESTICULAR."Year" = TESTICULAR_POPS.year
AND ((GDO_TESTICULAR."Age" = 'All' AND TESTICULAR_POPS.age = 'All Ages') OR
 (GDO_TESTICULAR."Age" = TESTICULAR_POPS.age))
 
order by "Year", "Tumour Type", "Stage", "Age"
 ;