ADDING THE COUNT OF PATENTS

[misslord]

Hello everybody,
I'm a beginner and I'm interested in downloading data on patents in ICTs technology fields, according to the IPC classification, that have been filled in Europe during the years 2010-2022 (in particular, who are the applicants and the number of their patents).
I'm having trouble adding the count of the number of patents to this query, as I get always the same error if I try to include count function.

Code: Select all

SELECT  pers.psn_name, p.publn_date, publn_auth, publn_nr, person_ctry_code
FROM tls211_pat_publn p
JOIN tls209_appln_ipc i ON p.appln_id = i.appln_id
JOIN tls207_pers_appln pa ON p.appln_id = pa.appln_id
JOIN tls206_person pers ON pa.person_id = pers.person_id 
WHERE (i.ipc_class_symbol LIKE 'G06%'
               OR i.ipc_class_symbol LIKE 'H03%'
               OR i.ipc_class_symbol LIKE 'H04%'
               OR i.ipc_class_symbol LIKE 'H01L%'
               OR i.ipc_class_symbol LIKE 'H01P%'
               OR i.ipc_class_symbol LIKE 'H01Q%'
               OR i.ipc_class_symbol LIKE 'H05K%'               
               OR i.ipc_class_symbol LIKE 'G08C%'
               OR i.ipc_class_symbol LIKE 'G09F%'
               OR i.ipc_class_symbol LIKE 'G09G%'
               OR i.ipc_class_symbol LIKE 'G10L%'
               OR i.ipc_class_symbol LIKE 'G11%'
               ) -- Change IPC symbol here
AND publn_auth= 'EP'
AND YEAR(p.publn_date) BETWEEN  '2010' AND '2022' 
AND pa.applt_seq_nr > 0 -- i.e. applicants only
GROUP BY psn_name, p.publn_date, publn_auth, publn_nr, person_ctry_code
ORDER BY publn_date, publn_nr, publn_auth DESC

Can you help me? I would be very grateful to anyone who will do it.

Thank you in advance!

[EPO / PATSTAT Support]

Hello Misslord,

Your query is only making a list of the patent publications, not a counting of number of patents.
Here is a straightforward and simple adaptation of your query:

Code: Select all

SELECT  pers.psn_name, count(distinct(p.appln_id)) patents 
FROM tls211_pat_publn p
JOIN tls209_appln_ipc i ON p.appln_id = i.appln_id
JOIN tls207_pers_appln pa ON p.appln_id = pa.appln_id
JOIN tls206_person pers ON pa.person_id = pers.person_id 
WHERE (i.ipc_class_symbol LIKE 'G06%'
               OR i.ipc_class_symbol LIKE 'H03%'
               OR i.ipc_class_symbol LIKE 'H04%'
               OR i.ipc_class_symbol LIKE 'H01L%'
               OR i.ipc_class_symbol LIKE 'H01P%'
               OR i.ipc_class_symbol LIKE 'H01Q%'
               OR i.ipc_class_symbol LIKE 'H05K%'               
               OR i.ipc_class_symbol LIKE 'G08C%'
               OR i.ipc_class_symbol LIKE 'G09F%'
               OR i.ipc_class_symbol LIKE 'G09G%'
               OR i.ipc_class_symbol LIKE 'G10L%'
               OR i.ipc_class_symbol LIKE 'G11%'
               ) -- Change IPC symbol here
AND publn_auth= 'EP'
AND YEAR(p.publn_date) BETWEEN  '2010' AND '2022' 
AND pa.applt_seq_nr > 0 -- i.e. applicants only
GROUP BY psn_name
ORDER BY patents DESC

But also have a look at other forum posts:
sql-queries-to-create-simple-applicant- ... able-10588

[misslord]

Thank you for your kind reply.

However, I need to have data on the count of number of patents AND also who are the applicants and the other info I put into the query as the publication date and the country code of applicant. Is it possible to have all these data?

If I try to add count command, I get the following error: Incorrect syntax near the keyword 'distinct'.

Thanks in advance.

[EPO / PATSTAT Support]

Hello misslord,
The published query works fine in PATSTAT Online and gives a list with applicant names and number of applications for those applicants listed. When making a "hitlist/ranking" with a ranking based on a number of patent applications per applicant, then it is not possible to have in the same list individual patents listed.
I have attached an excel sheet with the ranking and the SQL that was used to make the list.

resulttable-20230301143635.zip

(552.73 KiB) Downloaded 110 times

Here is a second SQL that produces a list of the individual patents; the SQL is constructed to give a limited list to 700.000 patent applications due the download limitation.

Code: Select all

SELECT  top 700000 *
FROM tls211_pat_publn p
JOIN tls207_pers_appln pa ON p.appln_id = pa.appln_id
JOIN tls206_person pers ON pa.person_id = pers.person_id 
WHERE  p.appln_id in (Select appln_id from tls209_appln_ipc i where i.ipc_class_symbol LIKE 'G06%'
               OR i.ipc_class_symbol LIKE 'H03%'
               OR i.ipc_class_symbol LIKE 'H04%'
               OR i.ipc_class_symbol LIKE 'H01L%'
               OR i.ipc_class_symbol LIKE 'H01P%'
               OR i.ipc_class_symbol LIKE 'H01Q%'
               OR i.ipc_class_symbol LIKE 'H05K%'               
               OR i.ipc_class_symbol LIKE 'G08C%'
               OR i.ipc_class_symbol LIKE 'G09F%'
               OR i.ipc_class_symbol LIKE 'G09G%'
               OR i.ipc_class_symbol LIKE 'G10L%'
               OR i.ipc_class_symbol LIKE 'G11%'
               ) -- Change IPC symbol here
AND publn_auth= 'EP'
AND YEAR(p.publn_date) BETWEEN  '2010' AND '2022' 
AND pa.applt_seq_nr > 0 -- i.e. applicants only
ORDER BY publn_date desc, p.appln_id, applt_seq_nr asc , invt_seq_nr asc

[misslord]

Thank you very much for the kind and helpful reply.

So, if I have understood, it is not possible to add in the same list other info related to the person applicant while counting for the number of patent applications.

[EPO / PATSTAT Support]

It is possible to add to the list some information, like the country & the sector of the applicant.
One can also via separate queries create other lists with a common data attribute so that tables can be joined. Also the SQL CASE statement allows some creativity on expanding tables.
But the output of an SQL query is always some kind of table. (think EXCEL) And that table should contain the data for your research. So starting from your data needs, one can build a query that produces a certain table. (or a set of tables). I simplify a bit the answer, because rather complex queries can lead to extensive tables. But the first step is to define in detail what data you need to do your analysis, so that it supports your research. Many examples are on the forum and are helpful to understand how data can be worked to support research questions.

Here is an example on the first query extended with the country and the sector:

Code: Select all

SELECT  pers.psn_name,pers.person_ctry_code, pers.psn_sector, count(distinct(p.appln_id)) patents 
FROM tls211_pat_publn p
JOIN tls209_appln_ipc i ON p.appln_id = i.appln_id
JOIN tls207_pers_appln pa ON p.appln_id = pa.appln_id
JOIN tls206_person pers ON pa.person_id = pers.person_id 
WHERE (i.ipc_class_symbol LIKE 'G06%'
               OR i.ipc_class_symbol LIKE 'H03%'
               OR i.ipc_class_symbol LIKE 'H04%'
               OR i.ipc_class_symbol LIKE 'H01L%'
               OR i.ipc_class_symbol LIKE 'H01P%'
               OR i.ipc_class_symbol LIKE 'H01Q%'
               OR i.ipc_class_symbol LIKE 'H05K%'               
               OR i.ipc_class_symbol LIKE 'G08C%'
               OR i.ipc_class_symbol LIKE 'G09F%'
               OR i.ipc_class_symbol LIKE 'G09G%'
               OR i.ipc_class_symbol LIKE 'G10L%'
               OR i.ipc_class_symbol LIKE 'G11%'
               ) -- Change IPC symbol here
AND publn_auth= 'EP'
AND YEAR(p.publn_date) BETWEEN  '2010' AND '2022' 
AND pa.applt_seq_nr > 0 -- i.e. applicants only
GROUP BY pers.psn_name,pers.person_ctry_code, pers.psn_sector
ORDER BY patents DESC