Help with SQL query - How to count the number of patent applications of a specific company

Here you can post your opinions, ask questions and share experiences on the PATSTAT product line. Please always indicate the PATSTAT edition (e.g. 2015 Autumn Edition) and the database (e.g. PATSTAT Online, MySQL, MS SQL Server, ...) you are using.
Post Reply

khanhhienbi
Posts: 13
Joined: Thu Sep 03, 2020 9:49 am

Help with SQL query - How to count the number of patent applications of a specific company

Post by khanhhienbi » Mon Nov 09, 2020 12:24 pm

Hi everyone,

I am currently working with PATSTAT Spring 2020 edition. I am currently looking at the number of total granted patents of a specific company from 2000 to 2019.

I also would like to add the results as a column next to my existing table if possible:

Code: Select all

SELECT t1.appln_id, t1.appln_auth, t1.appln_filing_date, t2.ipc_class_symbol, t3.appln_title
FROM
 tls201_appln t1
 JOIN
 tls209_appln_ipc t2 ON t1.appln_id = t2.appln_id
 JOIN
 tls202_appln_title t3 ON t3.appln_id = t1.appln_id
WHERE
t2.ipc_class_symbol LIKE 'C12N%'
AND t1.appln_auth IN ('GB')
AND t1.appln_filing_date between '2016-01-01' and '2020-01-01'
I am not sure how to obtain the results since PATSTAT is quite new to me.

Any advice is much appreciated. Many thanks in advance.


EPO / PATSTAT Support
Posts: 425
Joined: Thu Feb 22, 2007 5:33 pm
Contact:

Re: Help with SQL query - How to count the number of patent applications of a specific company

Post by EPO / PATSTAT Support » Tue Nov 10, 2020 9:52 am

Hello khanhhienbi,
first a couple of observations:
Patents that have been filed between '2016-01-01' and '2020-01-01' will mostly not be granted yet. It easily takes 3 to 4 years for a patent to become granted. So I have widened the scope, but you can easily adapt it to your needs.
If you want to "count" the number of patents, there is no need to JOIN the title of the application. So I have removed that table from the query.
And if you want to produce some kind of applicant ranking table, then you need to JOIN the tls206_person table in order to have the applicant names available for your ranking.

On the PATSTAT webpage, there are some publications with examples and sample queries to get started.
And also this forum has plenty of working sql queries that have been published to help researchers.

Code: Select all

SELECT psn_name, 
COUNT(distinct(case when t1.appln_filing_year = 2010 then t1.appln_id end)) as '2010',
COUNT(distinct(case when t1.appln_filing_year = 2011 then t1.appln_id end)) as '2011',
COUNT(distinct(case when t1.appln_filing_year = 2012 then t1.appln_id end)) as '2012',
COUNT(distinct(case when t1.appln_filing_year = 2013 then t1.appln_id end)) as '2013',
COUNT(distinct(case when t1.appln_filing_year = 2014 then t1.appln_id end)) as '2014',
COUNT(distinct(case when t1.appln_filing_year = 2015 then t1.appln_id end)) as '2015',
COUNT(distinct(case when t1.appln_filing_year = 2016 then t1.appln_id end)) as '2016',
COUNT(distinct(case when t1.appln_filing_year = 2017 then t1.appln_id end)) as '2017',
COUNT(distinct(case when t1.appln_filing_year = 2018 then t1.appln_id end)) as '2018',
COUNT(distinct(case when t1.appln_filing_year = 2019 then t1.appln_id end)) as '2019',
count(t1.appln_id) total
FROM   tls201_appln t1 JOIN  tls209_appln_ipc t2 
	ON t1.appln_id = t2.appln_id
join tls207_pers_appln on t1.appln_id = tls207_pers_appln.appln_id
join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id
WHERE
t2.ipc_class_symbol LIKE 'C12N%'
AND t1.appln_auth IN ('GB')
AND t1.appln_filing_year between 2010 and 2019
and applt_seq_nr > 0 and invt_seq_nr = 0
and granted = 'y'
group by psn_name
order by total desc
Best regards,
Geert Boedt
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Post Reply