It is possible to create 2 totally independent counts in one query, thanks to T-SQLs (which is Microsoft's SQL dialect) ability to have sub queries not only in the WHERE or FROM clause, but also in the SELECT clause. (Just out of curiosity: Why would you like to have both counts in the same query, a...

Dear Nico, You really had bad luck. Just 2 days before your post all the web pages of the EPO home page related to patent information has been relaunched, with a different structure and of course different URLs. Consequently, many URLs contained in already published documents became invalid. In the ...

Hi Seb, The downward trend of German granted applications in your result in the last years is clearly visible, but I still think the data is fine. You must be aware that it takes years (in some cases more years than you would expect) for an application to get granted. So for a young application chan...

Hi Rachie, Last week every PATSTAT subscriber should have received an e-mail notification indicating where to find the amended tables. Please contact our PATSTAT help desk (patstat@epo.org) with your contact details to find out why we missed you. Thank you. The updated tables are available till abou...

Like you already did, it is good practice to check the quality of your data and the plausibility of your result. Basically your query is fine, but you did not consider some particularity of the German patent office. The DPMA also publishes granted EPs in their Bulletin using PUBLN_KIND code D1. Thes...

Dear Florian, I do not know the table from OECD you mentioned. But I can give some information on the data used in PATSTAT. Table TLS902_IPC_NACE2 contains a mapping / concordance between IPC symbols and NACE2 (= NACE revision2) codes, which is the result of a work of Eurostat in co-operation with K...

Many of the PATSTAT Raw Data users work on MySQL. Recently I was in contact with Simone Mainardi ( http://www.simonemainardi.com/ ), who created a handy tool to upload the PATSTAT Raw Data files into MySQL. Simone is happy to share his effort with the community and recently upgraded it to be compati...

Just my observations and hints: Searching in long texts with wild cards,which you do heavily in your query, is costly in PATSTAT Online. A relational database is not the ideal tool for searching text. In your query there is no need to put a wild card on the left side of the IPC symbol, like ipc_clas...

You guessed correctly. If APPLT_SEQ_NR and INVT_SEQ_NR are > 0 in the table TLS207_PERS_APPLN or table TLS227_PERS_PUBLN, then an applicant is also an inventor for that application. As you mentioned, this is quite often the case. As usual, there are several ways to model data. The PATSTAT model was ...

Daniel, This is the classical division-by-zero error, because you compute "SUM(1/(nb_applicants*1.00))". It will help if you include in your statement "AND nb_applicants > 0"; then your query will run fine. Still, I do not fully understand the situation. In principle, there exist...