Need help for counting patents only filed in one country

[the_trendicator]

Hello,

I'd like to count inventions that were filed only in one country, to exclude them. Eg, in the IPC code A61K36/00 about medicinal preparations from plants, ancient chinese medicine patents are over-numerous, and most often they are only filed in China.
So I'd like to exclude them for proper statistics.
Below is the SQL query to select those CN patents, but I'm not sure it is correct because in PATSTAT online 2015b, it yields a small numbers of patents:

SELECT priority.earliest_filing_year, COUNT(DISTINCT(ap.prior_appln_id))
FROM tls201_appln AS a JOIN tls204_appln_prior AS ap ON a.appln_id = ap.appln_id JOIN tls201_appln AS priority ON ap.prior_appln_id = priority.appln_id JOIN tls209_appln_ipc AS code ON ap.prior_appln_id = code.appln_id
WHERE a.appln_auth LIKE 'CN' AND priority.appln_auth LIKE 'CN' AND LEFT(code.ipc_class_symbol,8) LIKE 'A61K 36'
GROUP BY priority.earliest_filing_year
ORDER BY priority.earliest_filing_year DESC ;

Some results below:
earliest_filing_year / Count(Distinct(ap.prior_appln_id))
2014 19
2013 87
2012 98
2011 83
2010 88
2009 40
2008 40
2007 46
2006 55
2005 34

Then, to count the number of patents having the code A61K36 without patents only filed in China, I have the query below, which seems also wrong to me.
SELECT priority.earliest_filing_year, COUNT(DISTINCT(ap.prior_appln_id))
FROM tls201_appln AS a JOIN tls204_appln_prior AS ap ON a.appln_id = ap.appln_id JOIN tls201_appln AS priority ON ap.prior_appln_id = priority.appln_id JOIN tls209_appln_ipc AS code ON ap.prior_appln_id = code.appln_id
WHERE a.appln_auth != 'CN' AND priority.appln_auth != 'CN' AND LEFT(code.ipc_class_symbol,8) LIKE 'A61K 36'
GROUP BY priority.earliest_filing_year
ORDER BY priority.earliest_filing_year DESC ;

Some results from Patstat Online 2015b:
earliest_filing_year / Count(Distinct(ap.prior_appln_id))
2014 7
2013 243
2012 316
2011 357
2010 356
2009 359
2008 343
2007 371
2006 375
2005 413

[Geert Boedt]

In principle there is nothing wrong with the query. The only thing one has to keep in mind when joining the application table (tls201) with the priority table (tls204_appln_prior), is that you will only retain applications that effectively have a priority filing. With other words, you exclude all first filings which results in much lower figures (especially for CN applications.)