EPO - Discussion forums

Posted: **Wed May 10, 2017 1:32 pm**

Hello,

i have two questions regarding the "group by" command. (patstat 2016b)

1. May anyone outline what is neccessary to make a query with a "group by" command run successfully?

2. Maybe you could help me with my specific query directly:

In the following query I would like to summarize every result with the same appl id in a single row. Therefore I think I need the group by key. Thank you in advance and kind regards.

select DISTINCT a.appln_id,a.appln_auth, IPR_type, granted, docdb_family_id, nb_applicants, more1.number_applt as Anzahl_Unternehmen,
nb_inventors, publn_kind, publn_date, c.person_id, d.person_id, psn_sector, psn_name, d.person_ctry_code, e.ctry_code, e.epo_member

From tls201_appln as a
join tls211_pat_publn as b on a.appln_id=b.appln_id
join tls207_pers_appln as c on a.appln_id=c.appln_id
join tls206_person as d on c.person_id=d.person_id
join tls801_country as e on a.appln_auth=e.ctry_code
join (
select appln_id, count (applt_seq_nr) number_applt
from tls207_pers_appln

join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id

where applt_seq_nr > 0 and invt_seq_nr = 0 and psn_sector in ('COMPANY',
'COMPANY GOV NON-PROFIT',
'COMPANY GOV NON-PROFIT UNIVERSITY',
'COMPANY HOSPITAL',

'COMPANY UNIVERSITY',
'GOV NON-PROFIT',
'GOV NON-PROFIT HOSPITAL',
'GOV NON-PROFIT UNIVERSITY',
'HOSPITAL',
'UNIVERSITY',
'UNIVERSITY HOSPITAL')
group by appln_id
having count(applt_seq_nr)>=2) as more1 on a.appln_id = more1.appln_id

where
ipr_type = 'PI'
and publn_date between '2015-01-01' and '2016-06-30'
and (epo_member = 'Y' or appln_auth = 'EP')
and more1.number_applt >= 2
and psn_sector in ('COMPANY',
'COMPANY GOV NON-PROFIT',
'COMPANY GOV NON-PROFIT UNIVERSITY',
'COMPANY HOSPITAL',
'COMPANY UNIVERSITY',
'GOV NON-PROFIT',
'GOV NON-PROFIT HOSPITAL',
'GOV NON-PROFIT UNIVERSITY',
'HOSPITAL',
'UNIVERSITY',
'UNIVERSITY HOSPITAL')

Order by a.docdb_family_id, a.appln_auth, b.publn_kind desc

Posted: **Tue May 16, 2017 10:49 am**

Hi,

I think you should first try to understand how to use the "group by" clause, for example by looking at http://www.zentut.com/sql-tutorial/sql-group-by/ or even youtube videos, that might be more begginer-friendly (https://www.youtube.com/watch?v=E3rXVrTTROU).

Then, about your query... I didn't verify it, but if it's well-constructed, you can summarize by appln_id like this (tested on ORACLE):

Code: Select all

select appln_id, count(*) from (
select DISTINCT a.appln_id,a.appln_auth, IPR_type, granted, docdb_family_id, nb_applicants, more1.number_applt as Anzahl_Unternehmen,
nb_inventors, publn_kind, publn_date, c.person_id, d.person_id, psn_sector, psn_name, d.person_ctry_code, e.ctry_code, e.epo_member
From patstat2016b.tls201_appln a
join patstat2016b.tls211_pat_publn b on a.appln_id=b.appln_id
join patstat2016b.tls207_pers_appln c on a.appln_id=c.appln_id
join patstat2016b.tls206_person d on c.person_id=d.person_id
join patstat2016b.tls801_country e on a.appln_auth=e.ctry_code
join (
select appln_id, count (applt_seq_nr) number_applt
from patstat2016b.tls207_pers_appln
join patstat2016b.tls206_person on tls207_pers_appln.person_id = tls206_person.person_id
where applt_seq_nr > 0 and invt_seq_nr = 0 and psn_sector in ('COMPANY',
'COMPANY GOV NON-PROFIT',
'COMPANY GOV NON-PROFIT UNIVERSITY',
'COMPANY HOSPITAL',
'COMPANY UNIVERSITY',
'GOV NON-PROFIT',
'GOV NON-PROFIT HOSPITAL',
'GOV NON-PROFIT UNIVERSITY',
'HOSPITAL',
'UNIVERSITY',
'UNIVERSITY HOSPITAL')
group by appln_id
having count(applt_seq_nr)>=2) more1 on a.appln_id = more1.appln_id
where
ipr_type = 'PI'
and publn_date between to_date('2015-01-01', 'YYYY-MM-DD') and to_date('2016-06-30', 'YYYY-MM-DD')
and (epo_member = 'Y' or appln_auth = 'EP')
and more1.number_applt >= 2
and psn_sector in ('COMPANY',
'COMPANY GOV NON-PROFIT',
'COMPANY GOV NON-PROFIT UNIVERSITY',
'COMPANY HOSPITAL',
'COMPANY UNIVERSITY',
'GOV NON-PROFIT',
'GOV NON-PROFIT HOSPITAL',
'GOV NON-PROFIT UNIVERSITY',
'HOSPITAL',
'UNIVERSITY',
'UNIVERSITY HOSPITAL')
Order by a.docdb_family_id, a.appln_auth, b.publn_kind desc)
group by appln_id
order by 2 desc;

EPO - Discussion forums

problems with "group by" in my query

problems with "group by" in my query

Re: problems with "group by" in my query