problems with "group by" in my query

Here you can post your opinions, ask questions and share experiences on the PATSTAT product line. Please always indicate the PATSTAT edition (e.g. 2015 Autumn Edition) and the database (e.g. PATSTAT Online, MySQL, MS SQL Server, ...) you are using.
Post Reply

Henning
Posts: 1
Joined: Wed May 10, 2017 12:46 pm

problems with "group by" in my query

Post by Henning » 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


Fr3dY
Posts: 26
Joined: Mon Oct 17, 2016 8:57 am

Re: problems with "group by" in my query

Post by Fr3dY » 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;


Post Reply