To create NUTS 2 based data, users need to aggregate the data from the lower level 3 to level 2.
Concrete: if you want to have data for NUTS 2 = DE11 Stuttgart, you will need to sum up all the data from the lower NUTS 3 codes for Stuttgart. Those will be: DE111: Stuttgart-Stadtkreis, DE112 Böblingen, DE113 Esslingen, DE114 Göppingen, DE115 Ludwigsburg, DE116 Rems-Murr-Kreis, DE117 Heilbronn-Stadtkreis, DE118 Heilbronn-Landkreis, DE119 Hohenlohekreis, DE11A Schwäbisch Hall, DE11B Main-Tauber-Kreis, DE11C Heidenheim and DE11D Ostalbkreis. (The complete lists for all NUTS codes is available in tls904_nuts).
The "adding up together" in SQL is done by "grouping" at the right level.
Here is the SQL code that counts the number of granted patents filed at the EPO, having (at least 1) applicant from Germany.
Code: Select all
SELECT nuts2.nuts , nuts2.nuts_label,
COUNT(distinct(case when year(publn_date) = 2005 then tls211_pat_publn.appln_id end)) as "2005",
COUNT(distinct(case when year(publn_date) = 2006 then tls211_pat_publn.appln_id end)) as "2006",
COUNT(distinct(case when year(publn_date) = 2007 then tls211_pat_publn.appln_id end)) as "2007",
COUNT(distinct(case when year(publn_date) = 2008 then tls211_pat_publn.appln_id end)) as "2008",
COUNT(distinct(case when year(publn_date) = 2009 then tls211_pat_publn.appln_id end)) as "2009",
COUNT(distinct(case when year(publn_date) = 2010 then tls211_pat_publn.appln_id end)) as "2010",
COUNT(distinct(case when year(publn_date) = 2011 then tls211_pat_publn.appln_id end)) as "2011",
COUNT(distinct(case when year(publn_date) = 2012 then tls211_pat_publn.appln_id end)) as "2012",
COUNT(distinct(case when year(publn_date) = 2013 then tls211_pat_publn.appln_id end)) as "2013",
COUNT(distinct(case when year(publn_date) = 2014 then tls211_pat_publn.appln_id end)) as "2014",
COUNT(distinct(case when year(publn_date) = 2015 then tls211_pat_publn.appln_id end)) as "2015",
COUNT(distinct(case when year(publn_date) = 2016 then tls211_pat_publn.appln_id end)) as "2016",
COUNT(distinct(case when year(publn_date) = 2017 then tls211_pat_publn.appln_id end)) as "2017",
COUNT(distinct(case when year(publn_date) = 2018 then tls211_pat_publn.appln_id end)) as "2018",
COUNT(distinct(case when year(publn_date) = 2019 then tls211_pat_publn.appln_id end)) as "2019",
COUNT(distinct(case when year(publn_date) = 2020 then tls211_pat_publn.appln_id end)) as "2020",
COUNT(distinct(case when year(publn_date) = 2021 then tls211_pat_publn.appln_id end)) as "2021",
COUNT(distinct(case when year(publn_date) = 2022 then tls211_pat_publn.appln_id end)) as "2022",
COUNT(distinct(case when year(publn_date) = 2023 then tls211_pat_publn.appln_id end)) as "2023"
FROM tls211_pat_publn join tls227_pers_publn on tls211_pat_publn.pat_publn_id = tls227_pers_publn.pat_publn_id
join tls206_person on tls206_person.person_id = tls227_pers_publn.person_id
left join (select nuts, nuts_label from tls904_nuts where nuts_level = 2 ) nuts2 on nuts2.nuts = left(tls206_person.nuts,4)
--NUL value will group the totals for those applicants where no NUTS has been assigned (yet)
where publn_auth = 'ep' and publn_kind = 'B1'
and applt_seq_nr > 0
and year(publn_date) between 2008 and 2023
and tls206_person.nuts like 'DE%'
group by nuts2.nuts , nuts2.nuts_label
order by nuts2.nuts
Code: Select all
SELECT nuts3.nuts , nuts3.nuts_label,
COUNT(distinct(case when year(publn_date) = 2005 then tls211_pat_publn.appln_id end)) as "2005",
COUNT(distinct(case when year(publn_date) = 2006 then tls211_pat_publn.appln_id end)) as "2006",
COUNT(distinct(case when year(publn_date) = 2007 then tls211_pat_publn.appln_id end)) as "2007",
COUNT(distinct(case when year(publn_date) = 2008 then tls211_pat_publn.appln_id end)) as "2008",
COUNT(distinct(case when year(publn_date) = 2009 then tls211_pat_publn.appln_id end)) as "2009",
COUNT(distinct(case when year(publn_date) = 2010 then tls211_pat_publn.appln_id end)) as "2010",
COUNT(distinct(case when year(publn_date) = 2011 then tls211_pat_publn.appln_id end)) as "2011",
COUNT(distinct(case when year(publn_date) = 2012 then tls211_pat_publn.appln_id end)) as "2012",
COUNT(distinct(case when year(publn_date) = 2013 then tls211_pat_publn.appln_id end)) as "2013",
COUNT(distinct(case when year(publn_date) = 2014 then tls211_pat_publn.appln_id end)) as "2014",
COUNT(distinct(case when year(publn_date) = 2015 then tls211_pat_publn.appln_id end)) as "2015",
COUNT(distinct(case when year(publn_date) = 2016 then tls211_pat_publn.appln_id end)) as "2016",
COUNT(distinct(case when year(publn_date) = 2017 then tls211_pat_publn.appln_id end)) as "2017",
COUNT(distinct(case when year(publn_date) = 2018 then tls211_pat_publn.appln_id end)) as "2018",
COUNT(distinct(case when year(publn_date) = 2019 then tls211_pat_publn.appln_id end)) as "2019",
COUNT(distinct(case when year(publn_date) = 2020 then tls211_pat_publn.appln_id end)) as "2020",
COUNT(distinct(case when year(publn_date) = 2021 then tls211_pat_publn.appln_id end)) as "2021",
COUNT(distinct(case when year(publn_date) = 2022 then tls211_pat_publn.appln_id end)) as "2022",
COUNT(distinct(case when year(publn_date) = 2023 then tls211_pat_publn.appln_id end)) as "2023"
FROM tls211_pat_publn join tls227_pers_publn on tls211_pat_publn.pat_publn_id = tls227_pers_publn.pat_publn_id
join tls206_person on tls206_person.person_id = tls227_pers_publn.person_id
left join (select nuts, nuts_label from tls904_nuts where nuts_level = 3 ) nuts3 on nuts3.nuts = tls206_person.nuts
where publn_auth = 'ep' and publn_kind = 'B1'
and applt_seq_nr > 0
and year(publn_date) between 2008 and 2023
and tls206_person.nuts like 'DE%'
group by nuts3.nuts , nuts3.nuts_label
order by nuts3.nuts