Patent count and citation

[larshovdanmolden]

Hi

I am trying to extract applications for a certain country for a certain time interval. I am constructing a table showing the application number, name of applicant, and the citation for every patent, in addition to some other information. I am quite new to SQL and have been struggling to get what I am looking for. Here is the script I have been working on:

Code: Select all

SELECT t1.appln_id, COUNT(distinct t3.pat_publn_id) AS cites_3y,  t5.person_id, t6.person_id, t6.person_name, t6.doc_std_name_id, t6.psn_sector, t6.han_name
FROM
     tls201_appln t1
     
INNER JOIN 
(SELECT
appln_id, MIN(publn_date) AS earliest_date FROM
tls211_pat_publn
GROUP BY appln_id) t2 ON t1.appln_id = t2.appln_id

INNER JOIN
    tls211_pat_publn t2b ON t2b.appln_id = t2.appln_id
INNER JOIN
tls212_citation t3 ON t2b.pat_publn_id = t3.cited_pat_publn_id
INNER JOIN
tls211_pat_publn t4 ON t3.pat_publn_id = t4.pat_publn_id


INNER JOIN
tls207_pers_appln t5 ON t1.appln_id = t5.appln_id
INNER JOIN
tls206_person t6 ON t5.person_id = t6.person_id

I am quite confident that this is a beginners error, but would really appreciate any help you can provide.

Best
Lars H Molden

[Geert Boedt]

Hello Lars,
your query has no "group by" clause, which you always need to have when using a "count".
You do not specify any limitations (country, applicant, application years,....) through a WHERE clause, which basically means that you are trying to execute your query on all the applications from the complete data base. This would result in millions of records, which will probably exceed the server capabilities and and result in an aborted query.
At this link you can download a PATSTAT test data base in MS ACCESS with working sample queries that are described in the documentation. It is good practice to first test your queries on a smaller data set before applying it to your complete data sample.
https://publication.epo.org/raw-data/pr ... roductId=1
There are some more self-learning tools that are available in the "Further Information" tab:
http://www.epo.org/searching-for-patent ... .html#tab3