Two counts for different 'where' statements
Posted: Fri Feb 12, 2016 5:01 pm
Hi,
In essence I would like to know if it is possible in PATSTAT Online to create two counts, where each represents their own set of where statements?
As an example, I would like to investigate Vestas patents after 2010, here I would like to (in a single run) count the number of unique families in general and the number of families which have a granted document.
In the code below I have tried doing it by creating two tls201 tables, however, no matter what I do they get connected such that one influences the other.
Do you have any ideas or suggestions?
Thanks,
Carsten
In essence I would like to know if it is possible in PATSTAT Online to create two counts, where each represents their own set of where statements?
As an example, I would like to investigate Vestas patents after 2010, here I would like to (in a single run) count the number of unique families in general and the number of families which have a granted document.
In the code below I have tried doing it by creating two tls201 tables, however, no matter what I do they get connected such that one influences the other.
Do you have any ideas or suggestions?
Thanks,
Carsten
Code: Select all
SELECT Count(DISTINCT tls201_appln.docdb_family_id) AS families,
Count(DISTINCT tls201_appln_b.docdb_family_id) AS granted_families
FROM tls201_appln
JOIN tls207_pers_appln
ON tls201_appln.appln_id = tls207_pers_appln.appln_id
JOIN tls206_person
ON tls207_pers_appln.person_id = tls206_person.person_id
JOIN tls207_pers_appln AS tls207_pers_appln_b
ON tls206_person.person_id = tls207_pers_appln_b.person_id
JOIN tls201_appln AS tls201_appln_b
ON tls207_pers_appln_b.appln_id = tls201_appln_b.appln_id
WHERE tls201_appln.earliest_filing_year >= '2010'
AND tls201_appln.ipr_type = 'PI'
AND tls201_appln_b.earliest_filing_year >= '2010'
AND tls201_appln_b.ipr_type = 'PI'
AND tls201_appln_b.granted = '1'
AND ( person_name LIKE '%vestas%' )